File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Help need in query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Help need in query" Watch "Help need in query" New topic
Author

Help need in query

Sheelpa Gupta
Ranch Hand

Joined: Aug 14, 2007
Posts: 72
I have a table as follows

Id Name col1 col2 col3
11 Abhishek val1 val2 val3
12 Ajay val1 val2 val3
13 Meera val4 val5 val6
14 Vijay val4 val5 val6
15 Malik val4 val5 val6
16 Sunil val7 val8 val9
17 Mehul val7 val8 val9

and so on

I want to write a query such that I get a single row for duplicate records in col1, col2, and col3 and max value of Id. For example
12 Ajay val1 val2 val3
15 Malik val4 val5 val6
17 Mehul val7 val8 val9

Can anybody help me on this? Thank you in advance.


SCJP 5 <br />SCWCD <br />Next: ?
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2499
    
    8

You almost literally gave the answer in your question.
You do this by selecting the values you want to get,
using max(id) to get max value of id, and group by all fields you selected, except max(id).

For more info, read about sql aggregate.


OCUP UML fundamental and ITIL foundation
youtube channel
Mourouganandame Arunachalam
Ranch Hand

Joined: Oct 29, 2008
Posts: 396
Hi,

The exact query would be

select * from <<yourtable>> where id in (
select max(id) from <<yourtable>>
group by col1, col2, col3
)

replace <<yourtable>> with your actual table name

Mourougan


Mourougan
Open Source leads to Open Mind
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Help need in query