It's not a secret anymore!
The moose likes JDBC and Relational Databases 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 and Relational Databases
Bookmark "Help need in query" Watch "Help need in query" New topic

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

Joined: Dec 20, 2006
Posts: 2565

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
Mourouganandame Arunachalam
Ranch Hand

Joined: Oct 29, 2008
Posts: 396

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


Open Source leads to Open Mind
I agree. Here's the link:
subject: Help need in query
It's not a secret anymore!