Improve company productivity with a Business Account.Sign Up

x
?
Solved

T SQL Select with order by and join

Posted on 2009-07-01
7
Medium Priority
?
600 Views
Last Modified: 2012-05-07
I am trying to write a t-sql query where i am trying to select top 1 column order by desc and making a join with another table. Here's the sample of my desired output along with the table structure:

Table1
--------------------------------
ID      Comments      MyID            Date
1      A                  11            11:34PM
2      B                  22            12:00PM
3      C                  33            01:20AM
1      AA                  11            05:00PM
1      AAA            11            03:00AM


Table2
--------------------------------
ID      Name
1      X
2      Y
3      Z

Result
--------------------------------
ID      Comments      MyID              Name
1      AAA            11            X
2      B                  22            Y
3      C                  33            Z

I want a join from Table1 and Table2 with Table1 returning just top 1 row order by ID desc and join with Table2.
0
Comment
Question by:PuneetKSaxena
6 Comments
 
LVL 2

Expert Comment

by:sunil_mails
ID: 24752956
u ll need a column to uniquely identify each row of table 1
Assuming u r using MS SQL:
Alter Table Table1
add SrNo int identity

select b.*
from (select id,max(SrNo) as SrNo
from table1
group by id) a
inner join table1 b
        on a.SrNo=B.SrNo
inner join table2 C
        on b.id=c.id

In Oracle:
u hv to add a sequence in place of identity column
0
 
LVL 2

Expert Comment

by:sunil_mails
ID: 24752974
Alternatively u can use following query if u r sure that date column is unique and stores time till millisecond.

select b.*
from (select id,max([Date]) as MyDate
from table1
group by id) a
inner join table1 b
        on a.MyDate=B.MyDate
inner join table2 C
        on b.id=c.id
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24753129
can you try this one?


select b.id,a.comment,a.myid,b.name
from table1 as a join table2 as b
on a.id=b.id and a.date=
(
      select dt from
      (select MAX(ta.date) as dt,ta.id from TableA as ta where ta.id=b.id  group by id) as t
)
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 32

Expert Comment

by:awking00
ID: 24755192
I'm not sure I understand your question. You indicate "order by ID desc" but your desired output shows the ID field in ascending order. Also, what is the criteria being used to determine top 1? Is it the earliest time (date) or the longest comments?
0
 
LVL 6

Accepted Solution

by:
IncisiveOne earned 500 total points
ID: 24761704
I am with awking00 ... there is not enough specific info in the question to produce the posted result.  The SQL is easy, once you can specify what you want.
0
 
LVL 32

Expert Comment

by:awking00
ID: 25439713
I've been on two weeks vacation and didn't get the opportunity to see this, but I'm curious why a comment was selected as an accepted solution, especially when the comment included an agreement with my comment?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question