Select different fields than GROUP BY in SQL Server

Question

I am trying to run a query in SQL Server where the columns I select are different than how it is grouped. This query is used in SAP Business One.

The problem, is that if I group by all the same columns that are selected, nothing is grouped.

Here is my query (which will obviously not work).

SELECT 
   T0.[CardCode], T2.[SlpName] as 'Rep', 
   T0.[CardName] as 'Facility', 
   T1.[Address] as 'Attention', 
   T1.[Street] as 'Address 1', 
   T1.[Block] as 'Address 2', T1.[City] as 'City', 
   T1.[State] as 'State', 
   T1.[ZipCode] as 'Zip' 
FROM OCRD T0 
INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] 
INNER JOIN OSLP T2 ON T2.[SlpCode] = T0.[SlpCode] 
WHERE T1.[State] = 'FL' 
GROUP BY T0.[CardCode]

Any feedback would be appreciated.

Thanks,

If you have more than one value for other columns, for example, two CardName for same CardCode. How do you want display them? — EricZ, May 10 '13 at 18:51
I want it to work like MYSQL would work. I forgot to mention that CardCode is a primary key. So no two records are going to have the same CardCode. — user2336671, May 13 '13 at 12:58

score 0 · Answer 1 · answered May 10 '13 at 18:55

0

You can try like this...

Select CardCode,Rep, 
    Facility, 
    Attention, 
    Address 1, 
    Address 2,City, 
    State, 
    Zip from  (SELECT Row_Number() Over(Partition By T0.[CardCode] order by T0.[CardCode]) as Row,
    T0.[CardCode], T2.[SlpName] as 'Rep', 
    T0.[CardName] as 'Facility', 
    T1.[Address] as 'Attention', 
    T1.[Street] as 'Address 1', 
    T1.[Block] as 'Address 2', T1.[City] as 'City', 
    T1.[State] as 'State', 
    T1.[ZipCode] as 'Zip' 
    FROM OCRD T0 
    INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] 
    INNER JOIN OSLP T2 ON T2.[SlpCode] = T0.[SlpCode] 
    WHERE T1.[State] = 'FL' 
    ) a where row=1

answered May 10 '13 at 18:55

Amit Singh

8,039
20
29

AmitSingh, thanks for the post. That query does not work. Also it looks like you are trying to merely show one results. I want it to show multiple results. Like in MySQL when you group 2 tables together. I just only want to show one record per Company. So if Company A has 5 addresses, I only want to show Company A once, with any one of the address, does not matter which one. If it is not grouped, it will show Company A 5 times, and show all 5 of the address options. – user2336671 May 13 '13 at 13:13
@user2336671 its or that for what you are looknig....can you tell me what error it give you – Amit Singh May 13 '13 at 15:22

EricZ · Answer 2 · 2013-05-13T18:07:08.323

If you really don't care which value return. Here is an example

SELECT 
   T0.[CardCode], 
   T2.[SlpName] as 'Rep', 
   T0.[CardName] as 'Facility', 
   T1.[Address] as 'Attention', 
   T1.[Street] as 'Address 1', 
   T1.[Block] as 'Address 2', T1.[City] as 'City', 
   T1.[State] as 'State', 
   T1.[ZipCode] as 'Zip' 
FROM OCRD T0 
CROSS APPLY (SELECT TOP 1 * FROM CRD1 TT1 ON T0.[CardCode] = TT1.[CardCode] AND TT1.[State] = 'FL') T1
CROSS APPLY (SELECT TOP 1 * FROM OSLP TT2 ON TT2.[SlpCode] = T0.[SlpCode]) T2

Select different fields than GROUP BY in SQL Server

2 Answers2