• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help need in query

 
Sheelpa Gupta
Ranch Hand
Posts: 72
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jan Cumps
Bartender
Posts: 2584
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Mourouganandame Arunachalam
Ranch Hand
Posts: 396
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic