GROUP BY query text columns

Question

I'm using a query, I want to group them by 1 specific column. i get an error for other field witch are not listed in GROUP BY clause, yes i know i should tell Sql Server witch row of grouped by rows you should pick but i don't know how to do that. If that's int i use MIN() function and if its a text field i don't know what to do. I want to select all data and if videos.number,videos.title is replicated than get the first row of grouped rows. What this query do is: get all channels from packages according to client subscription. sometime different packages have same channels and I don't want this channel to be shown twice to client.

USE [DB]

 SELECT Videos.number,
        Videos.title,
        Videos.description,
        Videos.format,
        Videos.urlTwo AS url,
        Videos.icon,
        Videos.streamtype,
        'pin' AS password,
        Videos.categoryid,
        Videos.videotype,
   FROM subscription
        INNER JOIN packages_channels
            ON subscription.pkgid = packages_channels.package_id 
        INNER JOIN Videos 
            ON packages_channels.video_id = Videos.number
  WHERE (GETDATE() >= subscription.startdate) 
    AND (GETDATE() <= subscription.enddate) 
    AND (subscription.username = 'mon1')
  GROUP BY videos.number, Videos.title
  ORDER BY Videos.number asc

this is what it shows right now:

number  title        description  format    url                      icon 
101     channel1     101          101       URLstream1               iconPath1
101     channel1     101          101       URLstream1               iconPath1  
102     channel2     101          102       URLstream2               iconPath2
107     channel7     107          107       URLstream7               iconPath7
108     channel8     108          108       URLstream8               iconPath8

but the result i want to get is this:

   number  title        description  format    url                      icon 
    101     channel1     101          101       URLstream1               iconPath1  
    102     channel2     101          102       URLstream2               iconPath2
    107     channel7     107          107       URLstream7               iconPath7
    108     channel8     108          108       URLstream8               iconPath8

as you see channel1 its only once url,and icon is the problem. this fields are text

I can't show a real sql result for security reason.

Please show sample tables and a result for better understanding — Ullas, May 02 '14 at 08:17
check if the title for all `number = 101` is the same, i suspect that one of the `channel1` may contain a trailing space — Brett Schneider, May 02 '14 at 08:33

score 1 · Accepted Answer · edited May 02 '14 at 09:22

1

I don't think you really need a group by here since you are not using any aggregate function. Using distinct will hopefully do the job like below

 SELECT DISTINCT
        Videos.number,
        Videos.title,
        Videos.description,
        Videos.format,
        convert(varchar(max),Videos.urlTwo) AS url,
        convert(varchar(max),Videos.icon) as icon,
        Videos.streamtype,
        'pin' AS password,
        Videos.categoryid,
        Videos.videotype
   FROM subscription
        INNER JOIN packages_channels 
            ON subscription.pkgid = packages_channels.package_id
        INNER JOIN Videos 
            ON packages_channels.video_id = Videos.number
  WHERE (GETDATE() >= subscription.startdate)
    AND (GETDATE() <= subscription.enddate)
    AND (subscription.username = 'mon1')
  ORDER BY Videos.number asc

edited May 02 '14 at 09:22

Kevin Hogg

1,771
25
34

answered May 02 '14 at 08:25

Rahul

76,197
13
71
125

i get this error for all textfields The text data type cannot be selected as DISTINCT because it is not comparable. – user3189504 May 02 '14 at 08:27
Try removing one column at a time to find out which column is causing this. – wvdz May 02 '14 at 08:28
url and icon. but i cant remove them this fields are essential – user3189504 May 02 '14 at 08:32
@user3189504, out of the columns mentioned/fetched in the query; are there any column with datatype `text` present. If yes, then you will need to convert them to `varchar` – Rahul May 02 '14 at 08:32
i converted them all to varchar (250) and it works :) – user3189504 May 02 '14 at 08:37
@user3189504, good to hear that. don't forget to mark it as answer – Rahul May 02 '14 at 08:37

score 0 · Answer 2 · answered May 02 '14 at 08:26

If the other columns such as description and format are functionally dependant on the column you want to group on, you can simply include them in the group by clause.

Functional Dependancy means that the column will always have the same value for a certain value of the column it is functionally dependant on.

If they are not functionally dependant, then this simply will not work, because if you have different descriptions for one number, how is the SQL Server to determine which description to display?

One way to overcome this is by using MAX(Description) for instance. This makes an arbitrary choice for the description that happens to come last alphabetically.

GROUP BY query text columns

2 Answers2