query not returning a single record

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

General SQL Server Forums

New to SQL Server Programming

query not returning a single record

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2008-07-31 : 04:49:40

Hi, I have a table structure as follows:
Table 1:


EMPID   FirstName Lastname EmpType        Email       Status
11       ABC1	   XYZ1		P	 ABC1@AA.COM  ACT
11       ABC1	   XYZ1		P	 ABC1@AA.COM  LEA
12       ABC2	   XYZ2		P	 ABC2@AA.COM  LEA
12       ABC2	   XYZ2		P	 ABC2@AA.COM  LEA
13       ABC3	   XYZ3		P	 ABC3@AA.COM  ACT
14       ABC4	   XYZ4		P	 ABC4@AA.COM  DON
14       ABC4	   XYZ4		P	 ABC4@AA.COM  LEA
14       ABC4	   XYZ4		P	 ABC4@AA.COM  ACT
15       ABC5	   XYZ5		P	 ABC5@AA.COM  DON
15       ABC5	   XYZ5		P	 ABC5@AA.COM  DON

SELECT DISTINCT(EMPID), FIRSTNAME, LASTNAME, EMPTYPE, EMAIL, STATUS
still gives more than ONE rows.

Required output:


EMPID   FirstName Lastname EmpType        Email       Status
11       ABC1	   XYZ1		P	 ABC1@AA.COM  ACT
12       ABC2	   XYZ2		P	 ABC2@AA.COM  LEA
13       ABC3	   XYZ3		P	 ABC3@AA.COM  ACT
14       ABC4	   XYZ4		P	 ABC4@AA.COM  DON
15       ABC5	   XYZ5		P	 ABC5@AA.COM  DON

Please let me know how can I get the above output?

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-07-31 : 04:58:57

select distinct * from table1 where status='act'

Arnav
Even you learn 1%, Learn it with 100% confidence.

zion99
Posting Yak Master

141 Posts

Posted - 2008-07-31 : 05:10:26

sorry for the trouble... its not possible to use where clause with the condition = ACT... i just want to have the top 1 record of each employee...
i have updated the required output.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 05:14:49

quote:
Originally posted by zion99

sorry... its not possible to use where clause with the condition = ACT... i just want to have the top 1 record of each employee...

Define TOP 1 !

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 05:15:54

[code]SELECT t.EMPID, t.FIRSTNAME, t.LASTNAME, t.EMPTYPE, t.EMAIL, t.STATUS
FROM YOURTABLE t
INNER JOIN (SELECT EMPID,MIN(STATUS) AS MinStatus
FROM YOURTABLE
GROUP BY EMPID)tmp
ON tmp.EMPID=t.EMPID
AND tmp.MinStatus=t.STATUS [/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 05:47:01

quote:
i have modified the required output...

What you have in your sample data in your post there is just your representation of the records. Records are not stored in table in any particular order. So the query "select top 3 * from table" is meaningless without the ORDER BY clause.

KH
[spoiler]Time is always against us[/spoiler]

zion99
Posting Yak Master

141 Posts

Posted - 2008-07-31 : 06:36:47

i used the query given by visakh16 & it runs pretty fine... but then on further testing i got to know that there will still duplicate records in it. (actually contains around 500000 records, hence i was not able to get correct data

])

one approach i m thinking will be to use a temporary table & then copy the records back to the main table.
Is there a better approach so that with one query, i can get my required output


Table 1:


EMPID   FirstName Lastname EmpType        Email       Status
11       ABC1	   XYZ1		P	 ABC1@AA.COM  ACT
11       ABC1	   XYZ1		P	 ABC1@AA.COM  LEA
12       ABC2	   XYZ2		P	 ABC2@AA.COM  LEA
12       ABC2	   XYZ2		P	 ABC2@AA.COM  LEA
13       ABC3	   XYZ3		P	 ABC3@AA.COM  ACT
14       ABC4	   XYZ4		P	 ABC4@AA.COM  DON
14       ABC4	   XYZ4		P	 ABC4@AA.COM  LEA
14       ABC4	   XYZ4		P	 ABC4@AA.COM  ACT
15       ABC5	   XYZ5		P	 ABC5@AA.COM  DON
15       ABC5	   XYZ5		P	 ABC5@AA.COM  DON


Required output:

EMPID   FirstName Lastname EmpType        Email       Status
11       ABC1	   XYZ1		P	 ABC1@AA.COM  ACT
12       ABC2	   XYZ2		P	 ABC2@AA.COM  LEA
13       ABC3	   XYZ3		P	 ABC3@AA.COM  ACT
14       ABC4	   XYZ4		P	 ABC4@AA.COM  DON
15       ABC5	   XYZ5		P	 ABC5@AA.COM  DON

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 09:07:42

are you using SQL Server 2005 ?

KH
[spoiler]Time is always against us[/spoiler]

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-07-31 : 09:59:05

A) I think you misunderstand DISTINCT. It is not a function - it applies to all columns in the select
B) In your example why for empid 11 & 14 do you choose the one you do? You need to define that then express it in sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 10:22:53

quote:
Originally posted by zion99

i used the query given by visakh16 & it runs pretty fine... but then on further testing i got to know that there will still duplicate records in it. (actually contains around 500000 records, hence i was not able to get correct data ])

one approach i m thinking will be to use a temporary table & then copy the records back to the main table.
Is there a better approach so that with one query, i can get my required output

Table 1:

EMPID FirstName Lastname EmpType Email Status
11 ABC1 XYZ1 P ABC1@AA.COM ACT
11 ABC1 XYZ1 P ABC1@AA.COM LEA
12 ABC2 XYZ2 P ABC2@AA.COM LEA
12 ABC2 XYZ2 P ABC2@AA.COM LEA
13 ABC3 XYZ3 P ABC3@AA.COM ACT
14 ABC4 XYZ4 P ABC4@AA.COM DON
14 ABC4 XYZ4 P ABC4@AA.COM LEA
14 ABC4 XYZ4 P ABC4@AA.COM ACT
15 ABC5 XYZ5 P ABC5@AA.COM DON
15 ABC5 XYZ5 P ABC5@AA.COM DON

Required output:

EMPID FirstName Lastname EmpType Email Status
11 ABC1 XYZ1 P ABC1@AA.COM ACT
12 ABC2 XYZ2 P ABC2@AA.COM LEA
13 ABC3 XYZ3 P ABC3@AA.COM ACT
14 ABC4 XYZ4 P ABC4@AA.COM DON
15 ABC5 XYZ5 P ABC5@AA.COM DON

do you have any other unique valued column in your table?

zion99
Posting Yak Master

141 Posts

Posted - 2008-07-31 : 22:57:25

No.. the data must be obtained from a view & its not possible to change the anomalies in the view. i need to do all the cleansing at my end...

zion99
Posting Yak Master

141 Posts

Posted - 2008-07-31 : 22:59:52

quote:
Originally posted by LoztInSpace

A) I think you misunderstand DISTINCT. It is not a function - it applies to all columns in the select
B) In your example why for empid 11 & 14 do you choose the one you do? You need to define that then express it in sql

it is just an example. basically.. i want just one record of the same group

zion99
Posting Yak Master

141 Posts

Posted - 2008-07-31 : 23:01:24

quote:
Originally posted by khtan

are you using SQL Server 2005 ?

KH
[spoiler]Time is always against us[/spoiler]

YES

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-08-01 : 01:08:23

quote:
Originally posted by zion99

quote:
Originally posted by LoztInSpace

A) I think you misunderstand DISTINCT. It is not a function - it applies to all columns in the select
B) In your example why for empid 11 & 14 do you choose the one you do? You need to define that then express it in sql

it is just an example. basically.. i want just one record of the same group

Well that's exactly what you got. Your group is
EMPID, FIRSTNAME, LASTNAME, EMPTYPE, EMAIL, STATUS

It looks like you are actually asking for one row at random for each empid.

select * from
(
select row_number() over(partition by empid order by empid ) rn
,*
from table
) x
where
rn
=1

change the order by to something more suitable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources