SQL query

[#1]

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.

[Anonymous]

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

[anbu1984]

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)

[Peter_Cammaert]

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

[#5]

Date and amount has not impact on this. DB used is SQL.

[jagan]

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.