CODEWITHSUNDEEP
sqloracle-databasegroup-by
user1536435
user1536435

Reputation: 129

SQL select columns group by

If I have a table which is of the following format:

ID NAME NUM TIMESTAMP BOOL
1  A    5   09:50     TRUE 
1  B    6   13:01     TRUE
1  A    1   10:18     FALSE   
2  A    3   12:20     FALSE
1  A    1   05:30     TRUE
1  A    12  06:00     TRUE

How can I get the ID, NAME and NUM for each unique ID, NAME pair with the latest Timestamp and BOOL=TRUE.

So for the above table the output should be:

ID NAME NUM
1  A    5
1  B    6

I tried using Group By but I cannot seem to get around that either I need to put an aggregator function around num (max, min will not work when applied to this example) or specifying it in group by (which will end up matching on ID, NAME, and NUM combined). Both as far as I can think will break in some case.

PS: I am using SQL Developer (that is the SQL developed by Oracle I think, sorry I am a newbie at this)

Upvotes: 1

Views: 169

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460058

If you're using at least SQL-Server 2005 you can use the ROW_NUMBER function:

WITH CTE AS
(
   SELECT ID, NAME, NUM,
      RN = ROW_NUMBER()OVER(PARTITION BY ID, NAME ORDER BY TIMESTAMP DESC)
   FROM Table
   WHERE BOOL='TRUE'
)
SELECT ID, NAME, NUM FROM CTE
WHERE RN = 1

Result:

ID     NAME   NUM    
1       A      5     
1       B      6

Here's the fiddle: http://sqlfiddle.com/#!3/a1dc9/10/0

Upvotes: 2

Csaba Benko
Csaba Benko

Reputation: 1161

select t1.* 
from TABLE1 as t1 
left join
TABLE1 as t2 
on t1.name=t2.name and t1.TIMESTAMP>t2.TIMESTAMP
where t1.BOOL='TRUE' and t2.id is null

should do it for you.

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

select t1.* from table as t1 inner join
(
select NAME, NUM, max(TIMESTAMP) as TIMESTAMP from table
where BOOL='TRUE'
) as t2
on t1.name=t2.name and t1.num=t2.num and t1.timestamp=t2.timestamp
where t1.BOOL='TRUE'

Upvotes: 0

Related Questions