T SQL Select with order by and join

Posted on 2009-07-01
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:

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

ID      Name
1      X
2      Y
3      Z

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.
Question by:PuneetKSaxena
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

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

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

Expert Comment

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
LVL 31

Expert Comment

ID: 24753129
can you try this one?

from table1 as a join table2 as b
on and
      select dt from
      (select MAX( as dt, from TableA as ta where  group by id) as t
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

LVL 32

Expert Comment

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?

Accepted Solution

IncisiveOne earned 125 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.
LVL 32

Expert Comment

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?

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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