SQL - Selecting 10 records

[Bryster]

Hi new to forum I was wondering if there is a way of combining SQL statements or whether the following can be shortend:SELECT TOP TEN * FROM all_records WHERE fileid = '001'SELECT TOP TEN * FROM all_records WHERE fileid = '002'SELECT TOP TEN * FROM all_records WHERE fileid = '003'SELECT TOP TEN * FROM all_records WHERE fileid = '004'SELECT TOP TEN * FROM all_records WHERE fileid = '005'SELECT TOP TEN * FROM all_records WHERE fileid = '006'SELECT TOP TEN * FROM all_records WHERE fileid = '007'SELECT TOP TEN * FROM all_records WHERE fileid = '008'SELECT TOP TEN * FROM all_records WHERE fileid = '009'SELECT TOP TEN * FROM all_records WHERE fileid = '010'I have a database with 1000s of records and 10 different "fileids", all I want to do is select the first 10 records from each fileid. I have acheived this by doing individual SELECT queries, but if I had 100 fileids this would take a while any ideas much appreciated.Bryster

[justsomeguy]

If you want to get a different set of results for each code I think that's the only way to do it. You can do this:SELECT TOP TEN * FROM all_records WHERE fileid IN ('001', '002', '003')But that will get 10 records total, not 10 with each value. The other alternative would be to get all results from the database and process them in another language, which would cut down the load on the database but increase the load on the web server.Actually, you might be able to use GROUP BY for this. What database system are you using? Is this SQL Server 2005?

[Bryster]

Thank you for the reply, I believe it is SQL Server 5.What I am trying to acheive is some sort of "proof" database 10 of each "fileid", from a database with "all_records". For this time round what I have done is OK, but for future jobs I may struggle.

[aspnetguy]

There never was an SQL Server 5. MS skipped from 4.21 to 6.0. 6.0 was a complete rewrite for Windows NT back in the early 90's. I highly doubt you are using that. We'll work with the assumption you meant 2005.If you had a large number of fileids you would have to get creative with temporary tables and a cursor. I used the Northwind database for my example but you can easily adapt it to your tables.

--create temporary table--to hold our selected ordersCREATE TABLE #SelectedOrders (  OrderID int,  CustomerID char(5)  -- ... and so on)--placeholder for customer idDECLARE @cust char(5)--declare cursorDECLARE c1 CURSOR FORSELECT CustomerIDFROM Orders--open cursorOPEN c1-- get first customer idFETCH NEXT FROM c1INTO @cust--process first recordWHILE @@FETCH_STATUS = 0 BEGIN  --insert into temp table using customer id  INSERT INTO #SelectedOrders  SELECT TOP 5 OrderID, CustomerID --, ... and so on  FROM Orders  WHERE CustomerID = @cust  --get next customer id  FETCH NEXT FROM c1  INTO @custEND--clean up cursorCLOSE c1DEALLOCATE c1--display all selected ordersSELECT * FROM #SelectedOrders--clean up temp tableDROP TABLE #SelectedOrders

[Bryster]

Thanks for your help, and yes I did mean 2005.