Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a set of data
ID| Amount| Date | Status
1|xxx1|dd-MM-YYY|Confirmed
1|xxx2|dd-MM-YYY|Pending
1|xxx3|dd-MM-YYY|Pending
2|xxx4|dd-MM-YYY|Confirmed
2|xxx4|dd-MM-YYY|Pending
2|xxx4|dd-MM-YYY|Pending...
I want the records of the top 2 (i.e confirmed & 1st pending )in each ID is it possible in SQL query. Query experts please help.
Thanks in advance,
Sai.
Hi,
Use below code in script:
Table:
LOAD *,
Status as PendingStatus
From ....
where Status = 'Pending';
Concatenate
LOAD *,
Status as ConfirmedStatus
From ....
where Status = 'Confirmed';
Regards
Neetha
Select ID, Amount, Date ,Status From table Where Status = 'Confirmed' Or (ID,Date,Status) in
(Select ID,Max(Date) As Date,Status From table where Status = 'Pending' Group by ID,Status)
Hmmm.
"top" seems to refer to the position on the screen, as there is no indication that either Amount or Date play a part in qualifying the top "Pending" record.
To accomplish this, you must pick the "Pending" record with the lowest record number. Numbering records is highly dependent on your database system. So it would be nice if you could tell us the type of database you're using.
Peter
Date and amount has not impact on this. DB used is SQL.
Hi,
Check this query.
SELECT *
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date DESC) AS RecordNum,
ID, Amount, Date, Status
FROM TableName) AS Temp
WHERE RecordNum <= 2
Regards,
Jagan.