SELECT ... result from conditioned row

[radim]

Hi,
I use MsSQL 7.0
I have got easy table MyTable (order by NAME):

ID NAME AGE
-----------------
4 Boris 26
2 John 22
5 John 24 <---
3 John 23
1 Kevin 25
...

I would like retrieve 10 rows by SELECT statement, where first row has NAME = JOHN AND ID = 5.

SELECT TOP 10 * FROM MyTable WHERE NAME >= 'John' ORDER BY NAME

it's OK, but my first row is John with ID = 2, of course.
How do I do it ?

Thanks, radim

 

[SQLScholar]

hey,

You want to equal john no more then or equal to. Then you also need an "and ID=5". So it would be the below.

SELECT TOP 10 * FROM MyTable WHERE NAME = 'John' AND ID=5 ORDER BY NAME

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[radim]

Hi Dan.
MyTable is only example to explain situation. In other case table will has other data and I don't start with John of course.

In my example I know only, that first row in my result has Name = John and Id = 5, plus I want next 9 rows in order by name.
I want to use this code for scrolling the very large table in my browse - so to retrieve only little number rows from position which I know, of course.

so:
1. I want to equal john and more (in my example)
2. I don't know, how many rows with John is afore, but my first john must has id = 5

I think I must to use some subquery ( nest select )

thanks, radim

 

[SQLScholar]

how about a union query so something like


SELECT * FROM MyTable WHERE NAME = 'John' AND ID=5
union
SELECT TOP 9 * FROM MyTable ORDER BY NAME

Or am i still on the wrong path?

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[swampBoogie]

SELECT TOP 10 * 
FROM MyTable 
ORDER BY 
case when name = 'John' and ID = 5 then 0 else 1 end
, NAME

 

[radim]

I find that if exist more rows with field name = 'John'
and I have table ordered by NAME, rows are sorted by NAME + ID (id is primary key in MyTable). I think It must to use.

SELECT TOP 10 *
FROM MyTable where name >= 'John'
ORDER BY
case when name = 'John' and ID = 5 then 0 else 1 end
, FIRMA,ID

so, to this statement anywise add condition:
if name = 'John' than ID >= 5 endif

thanks, radim

 

[radim]

SELECT TOP 10 *
FROM MyTable where (name = 'John' and ID >= 5) or
(name !='John' and name > 'John')
ORDER BY
case when name = 'John' and ID = 5 then 0 else 1 end,
NAME,ID


it's ok.