January 28, 2009 at 6:48 am
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?
January 28, 2009 at 7:12 am
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
January 28, 2009 at 7:19 am
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')
January 28, 2009 at 7:25 am
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
January 28, 2009 at 7:27 am
the record that appears first in order
--Many thanks
January 28, 2009 at 7:33 am
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
January 29, 2009 at 8:08 pm
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.
January 29, 2009 at 10:17 pm
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
January 29, 2009 at 10:50 pm
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
January 29, 2009 at 10:56 pm
rog pike (1/29/2009)
nabajyoti.b (1/28/2009)
the record that appears first in orderselect 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
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
February 1, 2009 at 2:39 pm
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply