Top 1 Record

[bvolles]

Hello,
I am hoping someone can assist me here. I have searched on the web and cant seem to find what the actual issue is. I have the following select statement:

SELECT DISTINCTROW TOP 1 tblElectronicBilling.[PRIMARY FILE NAME], tblElectronicBilling.[Assigned To], tblElectronicBilling.[Assigned Date], tblElectronicBilling.File_Receipt_Date, tblElectronicBilling.File_Load_Date, tblElectronicBilling.File_Completion_Date, tblElectronicBilling.Import_Date
FROM tblElectronicBilling
WHERE (((tblElectronicBilling.[PRIMARY FILE NAME]) Is Not Null) AND ((tblElectronicBilling.[Assigned To]) Is Null) AND ((tblElectronicBilling.File_Receipt_Date) Is Not Null) AND ((tblElectronicBilling.File_Completion_Date) Is Null))
ORDER BY tblElectronicBilling.File_Load_Date;

I am aware some fields are not normalized this was not my design. So what I have created is a query that returns 1 record from the table. I have a button on the form so the user can assign themselves work. I want it sorted by the oldest File_Load_Date. My issue is when I put an ASC sort on that field I get 22 records even tho I have it setup to return 1. I take the sort off and I get my expected result but the File_Load_Date does not return the oldest date.

Any assistance is appreciated!!

 

[oleronesoftwares]

While i dont really understand all you wrote,i will suggest sorting desc as against asc.
or use the max function.

i used this and it returned only one record,i.e the record with the latest date go through the query below and see how u can apply it to yours

SELECT TOP 1 aa.id, aa.dateent
FROM aa
GROUP BY aa.id, aa.dateent
ORDER BY aa.dateent DESC;

 

[bvolles]

Thanks for your suggestion however the user wants it sorted oldest to newest so it has to be ASC sort.

 

[oleronesoftwares]

ok. then its asc u will use

 

[bvolles]

I do ASC and still get 43 records. I only want 1 record returned each time the query is run.

 

[spikepl]

TOP 1 grabs the records that have the top value in the ORDER BY. So if there is 43 records with the same date, you'll get 43 records returned. To discern, add something else to sort by, so you sort by two things and not just one. If you have some ID somewhere that would be good.

 

[bvolles]

^ that worked perfect! Thank you