May 5, 2008 at 7:10 am
All,
I have one senario.
I have two tables.
Emp:
MID Status Code
1 A 1X
25 A 1X
37 T 1X
42 T 2X
58 A 2X
61 A 3X
PDetails:
Code Remarks
1x First Employee
2x Gold Medalist
3x Journalist
Now i want to retrive the output like
Code Status Remarks
1x A FirstEmployee
2x T Gold Medalist
3x A Journalist
But when i joined the above two table , Status column is repeating more than one time.
My requirement is :
I want to retrieve the status from the first row of corresponding CODE.
Say for example,
If we take 1X , Status available is A & T,but i want to display A only because A comes first.
If we take 2x, Status available is T&A,but i want to display T only because T comes first for 2X.
Inputs are Highly appreciable !
karthik
May 5, 2008 at 7:15 am
Karthik,
Give a look at the TOP clause as part of the SELECT statement in books online. It might be best to use a subselect in which you use the combination of TOP based on matching code specifying the desired ORDER BY clause.
EDIT:
For Example:
declare @emp table
( MID integer,
Status char(1),
Code char(2),
unique (Code, MID)
)
insert into @emp
select 1, 'A', '1X' union all
select 25, 'A', '1X' union all
select 37, 'T', '1X' union all
select 42, 'T', '2X' union all
select 58, 'A', '2X' union all
select 61, 'A', '3X'
--select * from @emp
declare @PDetails table
( Code char(2) primary key,
Remarks varchar(20)
)
insert into @PDetails
select '1X', 'First Employee' union all
select '2X', 'Gold Medalist' union all
select '3X', 'Journalist'
--select * from @PDetails
select
Code,
( select top 1 status
from @emp b
where a.Code = b.Code
order by MID
) as Status,
Remarks
from @PDetails a
/* -------- Sample Output: --------
Code Status Remarks
---- ------ --------------------
1X A First Employee
2X T Gold Medalist
3X A Journalist
*/
Note:
In this method I added in an index on the CODE column of the EMP table; not having this index will cause the code to run MUCH slower.
May 5, 2008 at 7:34 am
By 'first', do you mean the one with the lowest MID? If so, this will work for you.
SELECT <Column List>
FROM Emp Inner join
(SELECT MIN(Mid) AS FirstMID, Code FROM Emp Group By Code ) Sub
ON Emp.Code = Sub.Code AND emp.Mid = Sub.FirstMID
INNER JOIN PDetails ON Emp.Code = PDetails.Code
The correlated subquery in the select clause will work, but it's a performance problem waiting to happen. The correlated subquery will run for each row of the outer query (RBAR in disguise). On small tables, fine. On larger ones, not so fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2008 at 7:35 am
Be very, very weary of this requirement. Saying you want this or that result because it come first is IMHO a very bad idea. Just because that's what comes out first today, doesn't mean that's what will come out first tomorrow. Unless there is an ORDER BY Clause specified, ordering of a result set is not guaranteed. I can't tell you how many times I've had to correct an application because the original developer decided to anticipate the order of the results without specifying an order by clause. Changing and index or any number of modifications to your data could force them to be returned to you in an order that you are not expecting.
-Luke
May 5, 2008 at 7:42 am
Kent,
select
Code,
( select top 1 status
from @emp b
where a.Code = b.Code
order by MID
as Status,
Remarks
from @PDetails a
Suppose if MID order has changed what will happen ?
Gila,
I do agree your point. Because both table has more than 5000000 rows. Will our correlated subquery give good performance ? I think 'No'.
Also,
By 'first', do you mean the one with the lowest MID? If so, this will work for you.
No.MID has any value,I look at only CODE.
:blink:
karthik
May 5, 2008 at 7:44 am
Luke,
IMHO ?
karthik
May 5, 2008 at 7:48 am
Is MID an identity column? If this is not ordered by MID (within code of course) how do define what is "first"?
May 5, 2008 at 7:51 am
IMHO = In my humble opinion.
No.MID has any value,I look at only CODE
So how do you define the 'first' row for a specific code?
Remember, SQL Server has no concept of row ordering unless an order by is applied. Row position or insert order have no meaning.
5 million rows? That's a lot of employees.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2008 at 8:04 am
Is MID an identity column? If this is not ordered by MID (within code of course) how do define what is "first"?
No.
IMHO = In my humble opinion.
Thanks Gila.
5 million rows? That's a lot of employees.
Yes.
Just i am thinking why can't we go for TALLY table ?
say for example,
SELECT N,Emp,Code,count(*)
from Emp,Tally
group by N,Emp,Code
having N <= count(*)
But the problem is i am not getting increased N value.
karthik
May 5, 2008 at 8:11 am
I think your results are goint to be "unstable." It sounds to me like you are wanting to return information based on the physical order of "how the table lies." I think what we are trying to tell you is that your results might not be completely predictable. Am I off base here?
May 5, 2008 at 8:16 am
Unless you can definitely order your data by something other than "that's the way it exists in the table currently", you are not going to be able to write an accurate report. Perhaps if you shed some of the business logic behind the "first" bit we may be able to better assist you?
-Luke.
May 5, 2008 at 8:19 am
You need some column that defines which status is first for each code.
You cannot depend on the order that the rows are returned as that will change.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2008 at 8:21 am
I completely agree with the last two comments.
May 6, 2008 at 3:56 am
I do agree all of your words.
But i dont have any IDENTITY column in that table, i want to hit the table as it is 'PHYSICAL ORDER BASED SEARCH'.
If i order it,I loose the requierd output.
You cannot depend on the order that the rows are returned as that will change.
what ever happened in the table ( insert or update or delete ), simply i want to retrieve the status of the first row for each CODE.This is my requirement.
karthik
May 6, 2008 at 4:01 am
There's no such thing as a physical order based search in SQL server. A table is, by definition, an unordered set.
If you don't have a column specifying the order, you'll need to add one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply