records with distinct IDs

Question

Post reply

records with distinct IDs

nabajyoti.b

SSCrazy

Points: 2177
More actions
January 28, 2009 at 6:48 am

#128273

THe structure of the table is as follows
emp_id emp_name address
e001 A x
e002 B y
e001 C z
e001 l x
Now, i want to display the records with distinct emp_ids i.e
the result of the query should be
emp_id emp_name address
e001 A x
e002 B y
e001 C z
and the record
e001 l x
should be excluded
How do i do that?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

not enough information;

what makes the record e001 A valid,

and the e001 I record invalid?

is there a primary key in the table?

is it alphabetical?

is it order of entry(remember SQL Server does not care or keep track of the order of entry....it might be discoverable if the table has an identitity() field)

This looks more like pseudo records than real records....

psuedo records=psuedo solutions and suggestions.

help yourself by being very specific with the structure and data:

Show us the actual table itself (CREATE TABLE WHATEVER.....) so we can see the primary key

to help us build a solution, give us some INSERT INTO WHATEVER....staterments so we can recreate your data

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nabajyoti.b SSCrazy Points: 2177 More actions · Answer 2

create table emp

(

emp_id char(4),

emp_name char(15),

emp_address char(15)

)

insert emp values('e001','A','x')

insert emp values('e002','B','y')

insert emp values('e003','C','z')

insert emp values('e001','l','x')

Lowell SSC Guru Points: 323518 More actions · Answer 3

excellent!

that helps a lot, thanks. I can script and test agaisnt your example data now.

but what about the business logic?

what makes the record "e001 A" important and valid, but the record

"e001 I" record invalid?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nabajyoti.b SSCrazy Points: 2177 More actions · Answer 4

the record that appears first in order

--Many thanks

Lowell SSC Guru Points: 323518 More actions · Answer 5

in that case, it is impossible, unless you modify your table to include an identity column.

create table emp

(

empID int identity(1,1) not null primary key,

emp_id char(4),

emp_name char(15),

emp_address char(15)

)

insert emp(emp_id,emp_name,emp_address) values('e001','A','x')

insert emp(emp_id,emp_name,emp_address) values('e002','B','y')

insert emp(emp_id,emp_name,emp_address) values('e003','C','z')

insert emp(emp_id,emp_name,emp_address) values('e001','l','x')

select min(emp_id),min(emp_name),min(emp_address)

from emp

group by emp_id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tim Wilson-Brown Ten Centuries Points: 1395 More actions · Answer 6

Lowell (1/28/2009)
in that case, it is impossible, unless you modify your table to include an identity column.

Which is not reliable either - what if the identity values wrap?

There should be another way of determining "first" (e.g. row creation date) that's not being shown in the pseudo-example above.

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 7

Tim Wilson-Brown (1/29/2009)
Lowell (1/28/2009)
in that case, it is impossible, unless you modify your table to include an identity column.
Which is not reliable either - what if the identity values wrap?
There should be another way of determining "first" (e.g. row creation date) that's not being shown in the pseudo-example above.

Unless you're talking about something totally different than what I'm thinking, Identity values don't ever "wrap".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

steve dassin SSCrazy Points: 2396 More actions · Answer 8

nabajyoti.b (1/28/2009)
the record that appears first in order

select emp_id,emp_name,emp_address,rank

from

(select emp_id,emp_name,emp_address,

row_number()over(partition by emp_address order by emp_name) as rank

from ##emp) as A

where rank=1

order by emp_address,emp_name

emp_id emp_name emp_address rank

------ --------------- --------------- --------------------

e001 A x 1

e002 B y 1

e003 C z 1

To fully understand the power of the analytic functions (ie. row_number()) is to appreciate the concept of a cursor, putting rows of a table in an order.

The Sql ranking OVERture

http://beyondsql.blogspot.com/2008/04/sql-ranking-overture.html

www.beyondsql.blogspot.com

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 9

rog pike (1/29/2009)
nabajyoti.b (1/28/2009)
the record that appears first in order
select emp_id,emp_name,emp_address,rank
from
(select emp_id,emp_name,emp_address,
row_number()over(partition by emp_address order by emp_name) as rank
from ##emp) as A
where rank=1
order by emp_address,emp_name
emp_id emp_name emp_address rank
------ --------------- --------------- --------------------
e001 A x 1
e002 B y 1
e003 C z 1
To fully understand the power of the analytic functions (ie. row_number()) is to appreciate the concept of a cursor, putting rows of a table in an order.
The Sql ranking OVERture
http://beyondsql.blogspot.com/2008/04/sql-ranking-overture.html
www.beyondsql.blogspot.com

Still, there is no concept of what is "first" in an RDBMS without either an IDENTITY or some temporal indicator. It could just as easily go the other way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tim Wilson-Brown Ten Centuries Points: 1395 More actions · Answer 10

Jeff Moden (1/29/2009)
Tim Wilson-Brown (1/29/2009)
Lowell (1/28/2009)
in that case, it is impossible, unless you modify your table to include an identity column.
Which is not reliable either - what if the identity values wrap?
There should be another way of determining "first" (e.g. row creation date) that's not being shown in the pseudo-example above.
Unless you're talking about something totally different than what I'm thinking, Identity values don't ever "wrap".

Sorry, I was wrong about the wrapping - I'm a recovering C programmer. :ermm:

Identity values cause an arithmetic overflow - they don't wrap.

CREATE TABLE IdTest (

Id TINYINT IDENTITY(253,1),

Dummy INT

)

INSERT INTO IdTest VALUES(1) -- 253

INSERT INTO IdTest VALUES(1) -- 254

INSERT INTO IdTest VALUES(1) -- 255

INSERT INTO IdTest VALUES(1) -- Overflow