-2

I need to fetch items from my database with unique TrackingIDs and where the InternalID is between certain values. I would like to have all the rows content and not just the TrackingIDs

Basically I want something like this:

SELECT DISTINCT [TrackingID], *
  FROM [MyDataBase]
  Where [InternalID] <=45 AND [InternalID] >= 20
Nadeem_MK
  • 7,533
  • 7
  • 50
  • 61
shadonar
  • 1,114
  • 3
  • 16
  • 40
  • So what's going wrong? What have you tried? – the_pete Aug 20 '14 at 15:06
  • Doesn't your query work? Having any error? – Nadeem_MK Aug 20 '14 at 15:06
  • @the_pete well the above doesn't work because I have a good estimate of how many unique `TrackingIDs` I have and it returns far far too many, and I've tried doing a `Select` on a `Select Distinct` but that also failed. – shadonar Aug 20 '14 at 15:08
  • If you're looking for help, being snarky and sarcastic doesn't help. I asked what was going wrong and what you tried. Telling me the "above doesn't work" didn't answer the questions. The code you gave us looks like pseudocode so I wasn't sure what you have and haven't done. – the_pete Aug 20 '14 at 15:11
  • @the_pete I wasn't being snarky or sarcastic. I apologize if it appears that way. – shadonar Aug 20 '14 at 15:13
  • 1
    If you need a first record for each trackingID your question duplicates this one - http://stackoverflow.com/questions/7344731/sql-server-select-first-row-from-a-group – Bulat Aug 20 '14 at 15:14
  • @Bulat thanks for the info! that's what I'll be needing to do for another report I'm having to do next! – shadonar Aug 20 '14 at 15:27

2 Answers2

2

I haven't done this in MSSQL in a while so please forgive me if my syntax is incorrect but you could use a subquery:

select distinct [TrackingID],
max(InternalID)
from MyDataBase
where [TrackingID] in 
(select DISTINCT [TrackingID] as tracking
FROM [MyDataBase]
Where [InternalID] <=45 AND [InternalID] >= 20)
group by [TrackingID]

sqlfiddle link:http://sqlfiddle.com/#!6/61e176/6

I has to use the max() aggregate function to allow for the group by which gives me exactly the result set I was trying to get. Your mileage may vary as it seems that you are possibly getting back dirty data if you have "far far too many" "unique" ids being returned by your query. For a more exactly solution, please provide part of your data or result set.

the_pete
  • 822
  • 7
  • 19
0

You could use ROW_NUMBER in a CTE:

WITH CTE AS(
    SELECT TrackingID, InternalID, OtherColumns,
           RN = ROW_NUMBER() OVER (PARTITION BY TrackingID ORDER BY OrderColumn)
    From dbo.TableName
    Where [InternalID] <=45 AND [InternalID] >= 20
)
SELECT TrackingID, InternalID, OtherColumns
FROM CTE
WHERE RN = 1

Hers a demo: http://sqlfiddle.com/#!6/61e176/9/0

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939