Solved

SQL Query Using 1 column to group by with multiple columns in query

Posted on 2016-11-10
6
41 Views
Last Modified: 2016-11-10
I have the following view that I am trying to group by the column called fHEATNO but also show the other columns in the view with it. But it's telling me that the other fields are not part of an aggregate function. How can I group this by the fHEATNO column and still show the others? If there are multiple fHEATNO values then I can use the first record of those with multiple records with the same fHEATNO.


SELECT     TOP (100) PERCENT LTRIM(RTRIM(fHEATNO)) AS HEATNo, FSUFFIX, FVENDNO, FCOMPANY, FELEMC, FELEMMN, FELEMSI, FELEMP, FELEMS, FELEMCR, FELEMNI, 
                      FELEMMO, FELEMCU, FELEMCBTA, FELEMTI, FELEMCO, FELEMAL, FELEMSE, FELEMSN, FELEMV, FELEMN, FELEMB, FYIELDSTR1, FTENSILE1, FELONG1, 
                      FREDUC1, FSHIPHARD1, FHARDENABLE1, FMICROSTRUC1, FMACROTECH1, FGRSIZE1, FULTRASON1, FYIELDSTR2, FTENSILE2, FELONG2, FREDUC2, 
                      FSHIPHARD2, FHARDENABLE2, FMICROSTRUC2, FMACROTECH2, FGRSIZE2, FULTRASON2, RTRIM(FCAPABILITY) AS FCAPABILITY, FMILL, FCREATEDATE, 
                      FCOMMENTS, FPARTNO, FMELTTEMP, FHTTRTCOND, FCHANGEDATE, FMELTSOURCE, FCOUNTRY, FHEATLOT, FOVLOT, FCUSTHEATNO
FROM         dbo.HEATDATA
WHERE     (fHEATNO <> '')
GROUP BY fHEATNO
ORDER BY fHEATNO

Open in new window

0
Comment
Question by:Lawrence Salvucci
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41882997
The error message is telling you that GROUP BY must contain EVERY column that isn't using an aggregate function such as SUM() COUNT() MIN() MAX() etc.

This is by design and isn't a fault.

To achieve what I think you want needs a different technique, and I suggest ROW_NUMBER() for this. HOWEVER I will be guessing some of the detail.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41883007
Not that familiar with ROW_NUMBER. There are some fHEATNO values that are the same along with almost every other field except for the fpartno field. That would be the only one that would be different and I really don't need that field anyways. So how could I achieve to group these using the ROW_NUMBER?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41883014
You need to consider what conditions form "the first row". I have chosen a date column in descending order (so you get the "most recent" row) but as I don't know your data at all this is a guess:
SELECT
        *
FROM (SELECT
                LTRIM(RTRIM(fHEATNO))                                 AS HEATNo
              , ROW_NUMBER() OVER (PARTITION BY LTRIM(RTRIM(fHEATNO)) 
\                                  ORDER BY FCHANGEDATE DESC)         AS rn
              , FSUFFIX
              , FVENDNO
              , FCOMPANY
              , FELEMC
              , FELEMMN
              , FELEMSI
              , FELEMP
              , FELEMS
              , FELEMCR
              , FELEMNI
              , FELEMMO
              , FELEMCU
              , FELEMCBTA
              , FELEMTI
              , FELEMCO
              , FELEMAL
              , FELEMSE
              , FELEMSN
              , FELEMV
              , FELEMN
              , FELEMB
              , FYIELDSTR1
              , FTENSILE1
              , FELONG1
              , FREDUC1
              , FSHIPHARD1
              , FHARDENABLE1
              , FMICROSTRUC1
              , FMACROTECH1
              , FGRSIZE1
              , FULTRASON1
              , FYIELDSTR2
              , FTENSILE2
              , FELONG2
              , FREDUC2
              , FSHIPHARD2
              , FHARDENABLE2
              , FMICROSTRUC2
              , FMACROTECH2
              , FGRSIZE2
              , FULTRASON2
              , RTRIM(FCAPABILITY) AS FCAPABILITY
              , FMILL
              , FCREATEDATE
              , FCOMMENTS
              , FPARTNO
              , FMELTTEMP
              , FHTTRTCOND
              , FCHANGEDATE
              , FMELTSOURCE
              , FCOUNTRY
              , FHEATLOT
              , FOVLOT
              , FCUSTHEATNO
        FROM dbo.HEATDATA
        WHERE (fHEATNO <> '')
    ) AS D


WHERE RN = 1

Open in new window

Please note I do NOT recommend using ORDER BY in views, you should only apply an order by in the final select query. Using order in views is a performance problem.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41883028
The date column will work fine as the first record for the duplicates. I will test that out and post back shortly. I didn't know that about the ORDER BY in a view. Can that really impact the performance in a noticeable way?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41883034
Think about the ORDER BY this way....

select *
from atable t
inner join aview v on a.id = v.fk
where t.x = 'y' and v.cdate >= '20160101'
order by t.something, v.someother

any order by in that view is wasted effort because
1. you are joining to it
2. you filter out many rows
3. end up with a different order anyway
0
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 41883039
That makes sense. I'll have to go through all my views and remove the ORDER BY. Thank you for that insight! Appreciate it. And your query syntax works exactly how I wanted it to. Thank you very much!!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question