A query that has puzzled me...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

A query that has puzzled me...

Author

Topic

RichardBone
Starting Member

29 Posts

Posted - 2007-03-29 : 19:40:12

The table "People" has the following entries for the field "Name"
and "Gender"

Name Gender
Howie M
Alice F
Jim M
Bert M
Harry M

The question is Who's the 3rd male after Howie? Are they any special commands to help with these kind of queries? (Basic SQL with out T-SQL)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 19:45:52

What is "3rd"? Who's on first?

Peter Larsson
Helsingborg, Sweden

RichardBone
Starting Member

29 Posts

Posted - 2007-03-29 : 20:25:22

The name "Howie" could be anywhere in the table, the question is who is the 3rd male after Howie, in the entries I've given before, the query should give the result Harry because he's the third male after Howie.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 20:29:40

Third, according to what?
In a relational database management system, order of stored records has no meaning.
How do YOU determine order?

Peter Larsson
Helsingborg, Sweden

RichardBone
Starting Member

29 Posts

Posted - 2007-03-29 : 20:40:17

I guess the records will be sorted by autonumber increment field(1, 2, 3...) where the corresponding Name entries are (Howie, Alice, Jim...)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-29 : 21:58:09

[code]
declare @People table
(
[ID] int identity,
[Name] varchar(10),
Gender char(1)
)
insert into @People
select 'Howie', 'M' union all
select 'Alice', 'F' union all
select 'Jim', 'M' union all
select 'Bert', 'M' union all
select 'Harry', 'M'

select top 1 *
from
(
select top 3 *
from @People
where [ID] > (select [ID] from @People where Name = 'Howie')
and Gender = 'M'
order by [ID]
) p
order by [ID] desc
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-29 : 21:59:38

Will you have more than 1 Howie ? If so How do you want the result like ?

RichardBone
Starting Member

29 Posts

Posted - 2007-03-29 : 22:04:00

I don't want to do this in T-SQL, I want to do this using SQL statements. There is only 1 Howie.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-29 : 22:17:17

What is SQL Statement ? Why T-SQL is not SQL Statement ?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-29 : 22:22:56

Is this a homework question?

CODO ERGO SUM

RichardBone
Starting Member

29 Posts

Posted - 2007-03-29 : 23:54:21

Yes this is a HW question. And the professor said it can be done with out transactions, I don't see how.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 03:39:02

Not any of the suggestions here make use of transactions.

Peter Larsson
Helsingborg, Sweden

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-03-30 : 03:57:41

Maybe it's a trick question and the answer is actually what Peso said in his first reply. It's a pretty lame question if they're after SQL and it shows very little understanding of RDBMS - I'd be careful what you learn from this guy.
Also - never assume....

Wang
Starting Member

48 Posts

Posted - 2007-03-30 : 08:53:59

select top 1 name from
(
select top 3 * from people where name >= 'howie' and gender = 'm' order by name
) order by name desc

Might get you near where you need to be.

RichardBone
Starting Member

29 Posts

Posted - 2007-03-30 : 12:52:00

Yeah that is it what Peso wrote, I stopped reading the post after declare, assuming he was gonna use T-SQL iteration didn't realize there was a select statement at the end.

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-30 : 13:15:27

Are you learning more here, or in class?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 15:04:07

quote:
Originally posted by RichardBone

I guess the records will be sorted by autonumber increment field(1, 2, 3...) where the corresponding Name entries are (Howie, Alice, Jim...)

You can't guarantee that all values are used for IDENTITY column. Some records may be DELETEd.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 15:17:06

[code]-- Prepare sample data
DECLARE @People TABLE (ID INT, [Name] VARCHAR(10), Gender VARCHAR(1))

INSERT @People
SELECT 1, 'Howie', 'M' UNION ALL
SELECT 5, 'Alice', 'F' UNION ALL
SELECT 6, 'Jim', 'M' UNION ALL
SELECT 199, 'Bert', 'M' UNION ALL
SELECT 374, 'Harry', 'M'

-- Get "first" Howie
DECLARE @ID INT

SELECT @ID = MIN(ID)
FROM @People
WHERE [Name] = 'Howie'

-- Get next male (first after Howie)
SELECT @ID = MIN(ID)
FROM @People
WHERE ID > @ID
AND Gender = 'm'

-- Get next male (second after Howie)
SELECT @ID = MIN(ID)
FROM @People
WHERE ID > @ID
AND Gender = 'm'

-- Get next male (third after Howie)
SELECT @ID = MIN(ID)
FROM @People
WHERE ID > @ID
AND Gender = 'm'

-- Show the expected result
SELECT *
FROM @People
WHERE ID = @ID[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 15:19:58

[code]-- Prepare sample data
DECLARE @People TABLE (ID INT, [Name] VARCHAR(10), Gender VARCHAR(1))

INSERT @People
SELECT 1, 'Howie', 'M' UNION ALL
SELECT 5, 'Alice', 'F' UNION ALL
SELECT 6, 'Jim', 'M' UNION ALL
SELECT 199, 'Bert', 'M' UNION ALL
SELECT 374, 'Harry', 'M'

-- Get "first" Howie
DECLARE @ID INT,
@Step INT

SELECT @ID = MIN(ID),
@Step = 3
FROM @People
WHERE [Name] = 'Howie'

WHILE @Step > 0
-- Get next male
SELECT @ID = MIN(ID),
@Step = @Step - 1
FROM @People
WHERE ID > @ID
AND Gender = 'm'

-- Show the expected result
SELECT *
FROM @People
WHERE ID = @ID[/code]

Peter Larsson
Helsingborg, Sweden

GhantaBro
Posting Yak Master

215 Posts

Posted - 2007-03-31 : 11:18:58

What if we put all the males in a temptable with identity column in it and use the above logic.

Wang
Starting Member

48 Posts

Posted - 2007-04-02 : 05:41:17

Why not simply assume the only integral order for the system is alpha on name... then its easy.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources