May 23, 2012 at 8:59 am
hi all, newb here
I have several records in a view that aren't duplicate records, but have matching column values as other records.
I only want to select one of the records along with the other single records
EX.
item - itemA - retail
555 - 555L - 9.99
555 - 555XL - 9.99
555 - 555m - 9.99
763 - 763 - 9.99
462 - 462 - 9.99
222 - 222 - 9.99
my current stament for this view is:
SELECT top 200
item AS Expr1, itema AS Expr2, sdescript AS Expr3, retail AS Expr4, date_created AS Expr5, mfg AS Expr6
FROM dbo
WHERE (mfg <> 'somecompany')
ORDER BY date_created DESC
It doesn't matter which of the 555 lines, I just need one of them
I want the view to reflect:
555 - 555? - 9.99
763 - 763 - 9.99
462 - 462 - 9.99
222 - 222 - 9.99
I tried using distinct but couldn't seem to get it to work? please any help appreciated
May 23, 2012 at 9:37 am
you need to use a ranking function to find the first instance of a value
see my example below
select x.* from
(
select id,name,ROW_NUMBER() over (partition by id order by name asc) as isfirst from syscolumns
) x where isfirst =1
this finds the first item in syscolumns (alphabetically) for each distinct id (id is the id of the table)
MVDBA
May 26, 2012 at 9:43 am
Got it Michael. Thank you!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply