Solved

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

Posted on 2016-11-10
6
32 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sqlquerystress - To test db performance 8 41
Server 2012 VS Server 2016 for SQL Cluster 4 40
hyperlink data type in SQL 3 26
TSQL query to generate xml 4 32
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

776 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