August 12, 2009 at 12:12 am
I need to retrieve row number 3 and 4 in database with 5 rows for an example I am working on. The TOP command examples I have been getting only give me an option for selecting from the top rows but never a given row like the LIMIT 2,2 in Mysql where I can select from where I want to start. Can anyone help with how I can modify the TOP statement to give me values from certain rows.
August 12, 2009 at 12:49 am
Top's not going to work here. Since you're using SLQ 2005, you can use the ROW_NUMBER function. If you can post table structure and sample data I can write a tested query, but the general form will be something like this
SELECT <Column List>
FROM
(SELECT <Column List>, ROW_NUMBER() OVER (ORDER BY <SomeColumn>) AS RowNo
FROM <SomeTable>) sub
WHERE RowNo Between 3 and 4
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2009 at 10:05 am
MySQL tables...
database.names
id, name
1, Tom
2, Simon
3, Sarah
4, Ann
5, Bernie
database.telephone_nums
id, name_id, telephone_number
1, 2, 01438 234 678
2, 5, 01438 765 890
3, 1, 01438 657 897
database.mobile_nums
id, name_id, mobile_number
1, 1, 07913 452 675
2, 1, 07913 567 890
3, 3, 07913 545 555
1) Generate a single SQL query to return the telephone number for Tom.
2) Generate a single SQL query which returns the names of any people without either a telephone or mobile number stored.
3) Generate a query to return the second and third name from the lists sorted in ascending order. Do not do this by just listing the id numbers.
4) MySQL has the 'LIMIT' command which you probably used in No. 3. MS SQL has only the 'TOP' command instead. Try No. 3 for MS SQL instead.
Hope that makes sense,
August 12, 2009 at 10:14 am
Think I do that school work this is, right?
August 12, 2009 at 10:14 am
This is starting to look like homework. Is it?
If it is homework, I gave you the general form of a query that will solve question 3. If you have trouble, show what you've done and I'll help you further.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2009 at 11:18 am
Mish,
Are you in fact using MySQL rather than MS SQL Server 2005? Responses in this forum will assume the latter, as did Gail's suggested query using the Row_Number() function. Does MySQL support the Row_Number() function?
August 12, 2009 at 11:56 am
hmmm...
http://codingforums.com/showthread.php?p=849545
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 12, 2009 at 12:52 pm
I have done the questions and it was just question 4 I had problems with. I have just been told that SQL server does not have the LIMIT so question 4 is just a trick question. Though it seems like school work I have done a lot of research inot getting the answers hence I had asked for help with TOP command in SQL I did not ask for the answer to my questions.
So does MS SQL have a TOP command then similar to the LIMIT in mySQL?
August 12, 2009 at 1:33 pm
I am not familiar with MySQL, but can tell you that the TOP n command in SQL Server simply limits the results to the first n rows selected. If, for example, you have a table that may contain thousands of rows, you could retrieve just the first 10 by saying Select top 10 * from My_Results order by key_column The TOP command does not have a "starting with" parameter that would let you directly select two rows skipping the first one. I'm thinking you may have to put your starting point in the WHERE clause. For this, please show us what you did in MySQL and we could perhaps help with a "translation" to MS SQL.
August 12, 2009 at 1:33 pm
mish (8/12/2009)
So does MS SQL have a TOP command then similar to the LIMIT in mySQL?
MS SQL does have a TOP command. However, it does not have a LIMIT command. You can combine two TOP queries to mimic a LIMIT or you can use the ROW_NUMBER() function.
DECLARE @Foo TABLE (Val INT)
INSERT @Foo
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
SELECT TOP 3 *
FROM
(
SELECT TOP 5 *
FROM @Foo
ORDER BY Val
) AS T
ORDER BY Val DESC
SELECT Val
FROM
(
SELECT Val, ROW_NUMBER() OVER (ORDER BY Val) AS RowNum
FROM @Foo
) AS T
WHERE RowNum BETWEEN 3 AND 5 PS: Any chance of getting the CODE tags to allow new lines for spacing?? cripes!
August 12, 2009 at 1:37 pm
mish (8/12/2009)
So does MS SQL have a TOP command then similar to the LIMIT in mySQL?
No.
There are two options. You can either use two TOP statements, one in a subquery ordered one way around and one in the outer query ordered the other way around, or you can use the ROW_NUMBER function
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2009 at 1:42 pm
mish (8/12/2009)
I have done the questions and it was just question 4 I had problems with. I have just been told that SQL server does not have the LIMIT so question 4 is just a trick question. Though it seems like school work I have done a lot of research inot getting the answers hence I had asked for help with TOP command in SQL I did not ask for the answer to my questions.So does MS SQL have a TOP command then similar to the LIMIT in mySQL?
If it isn't school work, what is it?
you were also asked to show what you have done so far to answer your own questions. This would let us know where you are having difficulty and be able to provide better advice to you.
Personally, I'd use a CTE with the ROW_NUMBER() function and select rows 2 and 3.
August 12, 2009 at 3:45 pm
Thanks guys for your help I will probably try the Row number I dont have SQL server installed on my machine but I will probably install it tomorrow. My answer to Q3 was
SELECT name FROM database.names
ORDER BY name ASC
LIMIT 1, 2
Answer to Q4 was
SELECT TOP 2 name FROM database..names
ORDER BY name ASC
But that only returns the first 2 names thats why when I started the thread I wanted to know if there is anyway around. I know it may look like homework but the post has been Posted in SQL Sever Newbies. I can only study by examples and small questions how do I handle the same question when dealing with 500 entries in 10 tables so please the initial post question is for the TOP command I am not bothered about the answers to the questions and the tables I am using are for an example and I have answered all the questions to them.
August 12, 2009 at 4:18 pm
mish (8/12/2009)
Thanks guys for your help I will probably try the Row number I dont have SQL server installed on my machine but I will probably install it tomorrow. My answer to Q3 wasSELECT name FROM database.names
ORDER BY name ASC
LIMIT 1, 2
Answer to Q4 was
SELECT TOP 2 name FROM database..names
ORDER BY name ASC
But that only returns the first 2 names thats why when I started the thread I wanted to know if there is anyway around. I know it may look like homework but the post has been Posted in SQL Sever Newbies. I can only study by examples and small questions how do I handle the same question when dealing with 500 entries in 10 tables so please the initial post question is for the TOP command I am not bothered about the answers to the questions and the tables I am using are for an example and I have answered all the questions to them.
Just because you posted in the SQL Server Newbies forum doesn't really mean it wasn't homework. We've had individuals post homework in many of the forums. If it looks like homework to us, it probably is most of the time. We have been wrong on occasion.
August 13, 2009 at 1:00 pm
By strict definition, is a test homework?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply