query problem - SQL Server Forums

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)

query problem

Author

Topic

ind_sql
Starting Member

16 Posts

Posted - 2004-09-23 : 16:39:55

i have a table in which i have the following fields....emplid, name, age, sex, sequence.
e.g

emplid name age sex SEQUENCE
100 A 10 M 1
100 A 10 male 2
200 B 20 M 1
200 B 20 Mal 2
300 C 30 F 1
300 C 30 Fe 2
300 C 30 Fem 3

I want a query that would return me the single distinct record for each emplid according to the maximum sequence.

e.g i need the result

emplid name age sex SEQUENCE
100 A 10 male 2
200 B 20 Mal 2
300 C 30 Fem 3

what query should i write?????

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-23 : 16:43:07

Use GROUP BY with MAX.

Here's a start for you:

SELECT Column1, MAX(Column2)
FROM Table1
GROUP BY Column1

Tara

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-23 : 16:56:29

Select A.*
From table1 A
Inner Join
(
Select column1, column2 = max(column2) From table1 group by column1
) B
On A.column1 = B.column1
and column2 = B.column2

Corey

ind_sql
Starting Member

16 Posts

Posted - 2004-09-23 : 16:57:21

Posted - 09/23/2004 : 16:51:43
--------------------------------------------------------------------------------

i don't need only the emplid and max sequence....your query works fine if it is only for two coloumns....

the problem is i need all the data..... distinct emplid, age sex corresponding to the max sequence number. IF i group by coloumn 1 i get correct results for emplid and max sequence but not for sex....
e.g the sex should be male, mal , fem in the three cases respectively

help me

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-23 : 16:58:47

Use Corey's more detailed solution to get the other columns. You have to join to the query that I provided. That's the key.

Tara

ind_sql
Starting Member

16 Posts

Posted - 2004-09-23 : 17:06:32

not working still please help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-23 : 17:06:56

Please post your query.

Tara

ind_sql
Starting Member

16 Posts

Posted - 2004-09-23 : 17:18:56

the query is giving errors...syntax errors

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-23 : 17:21:04

Please post it.

Tara

ind_sql
Starting Member

16 Posts

Posted - 2004-09-23 : 17:24:42

select a.* from table1 a
Inner Join(Select name, sequence = max(column2) From table1 group by name
) B
On A.name = B.name
and sequence = B.sequence

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-23 : 17:26:35

try:

select a.* from table1 a
Inner Join(Select name, sequence = max(sequence) From table1 group by name
) B
On A.name = B.name
and sequence = B.sequence

Corey

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-23 : 17:29:27

Shouldn't you be grouping by emplid?



select a.* 
from table1 a
Inner Join
	(
		Select emplid, sequence = max(sequence) 
		From table1 
		group by emplid
	) B
On A.emplid = B.emplid and sequence = B.sequence

Tara

ind_sql
Starting Member

16 Posts

Posted - 2004-09-23 : 17:32:56

used exactly the same....the error is "choose the correct syntax near select name,sequence=max(sequence)"

select a.* from table1 a
Inner Join(Select name, sequence = max(sequence) From table1 group by name
) B
On A.name = B.name
and sequence = B.sequence

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-23 : 17:34:41

Is this MS SQL Server?

Tara

ind_sql
Starting Member

16 Posts

Posted - 2004-09-23 : 17:38:03

still not working

mssql

ind_sql
Starting Member

16 Posts

Posted - 2004-09-23 : 17:38:25

am using mssql

ind_sql
Starting Member

16 Posts

Posted - 2004-09-23 : 17:38:29

am using mssql

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-23 : 17:40:13

Try the query that I posted. It works fine on my laptop. I'm sure you'll need to change the table name though.

Tara

ind_sql
Starting Member

16 Posts

Posted - 2004-09-23 : 17:46:54

i have changed the table name also and tried this query in MS Access also...the error it gives is "expression of joins is not supported"
please help me

ind_sql
Starting Member

16 Posts

Posted - 2004-09-23 : 17:49:25

MS AccesS highlights sequence=B.sequence also

ind_sql
Starting Member

16 Posts

Posted - 2004-09-23 : 17:49:29

MS AccesS highlights sequence=B.sequence also

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources