Jump to content

SQL - Selecting 10 records


Bryster
 Share

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...