0

I know I am beating a dead horse here it seems like, but I have messed with this for an hour, trying all the examples I can find and nothing seems to be doing it for me. Below is a very dumbed down version of what I am going after. In my real world solution I am querying like 14 columns, with 2 joins and only like 3 conditions. 

select distinct 
        d.rental_ticket,
        i.Invoice_Number
from HP_View_DEL_Ticket_Header_Master as d
join CSView_INVC_Header_Master as i
on d.Rental_Ticket = i.Rental_Ticket_or_Tag_Number
where d.Ticket_Month <= '6'
and d.Ticket_Year = 2014
order by Rental_Ticket

I get something like this

Rental    Invoice
3023    3127
3146    3074
3215    3103
3235    3167
3245    3054 -- dup
3245    3055 -- dup
3249    3081
3251    3214
3255    3102
3261    3099
3267    3098
3276    3056

I know since I am using distinct with multiple columns it will filter down to all combinations. well like many, I just need to see the rental number once, no matter how many invoices it has.

in my live query, I am using a condition that is looking for a code, CRT, I only want to see one line of data for (in turn on rental number) no matter if there is only one or 10 CRT codes present

I threw this in there based on another person example but it seemed to do nothing

where d.Rental_Ticket in (select max(Rental_Ticket) as rental_ticket from HP_View_DEL_Ticket_Header_Master as d group by d.Rental_Ticket)

any help will be greatly appreciated!!

UPDATE:

select  d.rental_ticket, max(i.invoice_number) as Invoice_Number,
        d.Reference_Location1 as Rig, max(d.Rental_Ticket)
from HP_View_DEL_Ticket_Header_Master as d
join CSView_INVC_Header_Master as i
on d.Rental_Ticket = i.Rental_Ticket_or_Tag_Number
where d.Ticket_Month <= '6'
and d.Ticket_Year = 2014
group by d.Rental_Ticket, d.Reference_Location1
order by Rental_Ticket

this give me 4 columns, when really I am only going to need 2 (Rental_Ticket and Rig) thanks BD

bdrilling33
  • 57
  • 8
  • How do you want your output to be? I mean you want still two columns in the output? If yes, which one of the `Invoice` values you want to be returned? – Alireza Jul 16 '14 at 22:41
  • 1
    Until you define which Invoice you want to disregard, then we can only guess at an appropriate solution. Why are you even showing the invoice if you don't care which one you want to see. Normally people want to see the 'latest' record based on a date. – Nick.Mc Jul 16 '14 at 22:54
  • This was just an example of what my problem is. Because of the multiple invoices, I run into the problem of duplicate rentals. In my real life situation, along with this issue, I will be adding another join which is related to a category code, that is found on yet another join that is related to the rental ticket. this code is usually found only once, but just like with the invoices, there will be times when it is on there 2 or even three times. – bdrilling33 Jul 17 '14 at 12:36

2 Answers2

0

Replace distinct with group by and that will give you a whole bunch of options:

select d.rental_ticket,
    MIN(i.Invoice_Number) as Invoice_Number
from HP_View_DEL_Ticket_Header_Master as d
join CSView_INVC_Header_Master as i
on d.Rental_Ticket = i.Rental_Ticket_or_Tag_Number
where d.Ticket_Month <= '6'
and d.Ticket_Year = 2014
group by d.rental_ticket
order by Rental_Ticket
Alireza
  • 4,976
  • 1
  • 23
  • 36
0

I would do this:

select  d.rental_ticket, MAX(i.Invoice_Number)
from    HP_View_DEL_Ticket_Header_Master as d
        join CSView_INVC_Header_Master as i
        on d.Rental_Ticket = i.Rental_Ticket_or_Tag_Number
where   d.Ticket_Month <= '6'
        and d.Ticket_Year = 2014
GROUP BY d.rental_ticket
order by d.rental_ticket

Basically you want to get data for each unique Rental Ticket. The problem is that the server knows that you could have several invoice numbers for each rental ticket. So you group by the Rental Ticket to get only unique values.

For all the other columns you need to use an aggregate function. Something to take all those instances of invoice numbers and get just one for each grouping of rental tickets.

In my example I used MAX. Which gives you 3055 as the invoice number for the rental ticket of 3245.

If you don't want to use Group By then these answers have some alternatives.

Community
  • 1
  • 1
Vaccano
  • 78,325
  • 149
  • 468
  • 850
  • ok this works in my example, but is not working when I add more columns to the query. I get a column XXX is invalid in the select list because it is not contained in either an aggregate function or the group by function – bdrilling33 Jul 17 '14 at 14:37
  • @bdrilling33 - Each additional column you add needs to be in an aggregate function. Like Min or Max. That is because for each rental_ticket there can be many "AddtionalColumn" entries. Sql Server needs a way to pick one or otherwise get a single vale for the column. A full list of the Sql Server Aggregate functions can be found here: http://msdn.microsoft.com/en-us/library/ms173454.aspx – Vaccano Jul 17 '14 at 16:37
  • ok...so I need to have a Max for every column, and group by this column? Ill update the original post with what I am asking. also I am using a condition that is causing my issues as well?? do I have to put that into an aggregate as well? – bdrilling33 Jul 17 '14 at 16:59
  • @bdrilling33 - If you notice in my example that I have a where clause that uses other columns that are not in an aggregate function. So no, the where clause does not need to have the columns it uses in aggregate functions. – Vaccano Jul 17 '14 at 19:01
  • I understand, but one of my conditions are causing duplicate rental tickets. So I'm kind of back where I started – bdrilling33 Jul 18 '14 at 13:21