I have a query where I need to return rows but only if one columns first two characters are unique. I have this currently which works, but want to (and was told I should) do this without using MAX just so I can use the Group By. Here is my query. Can somone help me find the correct way to only return unique rows when the MajorVersion column is unique.

Select
max(sys.NetBios_Name0) as [ComputerName],
max(sys.user_Name0) as [Last Logged On User],
max(sys.AD_Site_Name0) as [Site],
max(slm.PrimaryUser0) as [Primary User],
max(hw.LastHWScan) as [Last HW Scan],
max(IE.Product0) as [ProductName],
LEFT(IE.FileVersion0,2) as [MajorVersion],
max(IE.FileVersion0) as [MinorVersion],
max(IE.InstalledFilePath0) as [FilePath],
max(IE.ExecutableName0) as [Executable Name],
(Select TOP(1) v_GS_CCM_RECENTLY_USED_APPS.LastUsedTime0
from v_GS_CCM_RECENTLY_USED_APPS with (NOLOCK)
left join v_R_System SYS1 on v_GS_CCM_RECENTLY_USED_APPS.ResourceID = SYS1.ResourceID
Where SYS1.ResourceID = SYS.ResourceID
AND v_GS_CCM_RECENTLY_USED_APPS.ExplorerFileName0 = IE.ExecutableName0
ORDER BY v_GS_CCM_RECENTLY_USED_APPS.LastUsedTime0 DESC
) AS [LASTACCESSED]
FROM v_GS_INSTALLED_EXECUTABLE IE
Left Join v_R_System SYS on sys.ResourceID = IE.ResourceID
left join v_GS_SallieMae0 slm on slm.ResourceID = IE.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS hw on hw.ResourceID = IE.ResourceID
Where
IE.ExecutableName0 like 'MSACCESS.EXE'
GROUP BY
LEFT(IE.FileVersion0,2),sys.ResourceID,IE.Executab leName0
ORDER BY ComputerName

Andrew Beller (andrew.beller (AT) salliemae (DOT) com) writes:
I'm afraid that I'm a little lost. What you do you mean with "do this
without using MAX just so I can use the Group By". You are using GROUP BY,
aren't you? MAX does not prevent you to use GROUP BY, rather you typically
need GROUP BY when you have MAX.

Nor do I really understand what you mean with "if one columns first
two characters are unique". I would interpret this as if you have the rows

Col1 Col2
Albert 2
Allan 8
Victoria 8
Viscount 10
Blenda 12
Hugo 12
Hubert 19
Peter 1

You should return the rows with Blenda and Peter. But that does not match
your query which you say is working.

Could you clarify?




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Hi.
I can clarify. I tried doing "Group By" on only the MajorVersion column, but I get all kinds of messages saying that the other columns must also be listed in the group by or be aggregate functions. Adding them all to the group by skew's my results.

I need to group the major version column (first two characters in the version string only) so I don't get multiple rows for say, "Version 10.0.1 and version 10.0.2" should only return one row since they are both version "10".

I was trying to figure out how to group by only one column.

Does that help?

Andrew Beller (andrew.beller (AT) salliemae (DOT) com) writes:
Grouping on one column is simple: you put that column in the GROUP BY
list, and all columns you put in some aggregate.

But I think you mean something else.

Say you have these rows:

IE.FileVersion IF.InstalledFilePath NetBios_Name0
10.0.1 C:\temp\thatfolder Albert
10.0.1 C:\temp\yetafolder Edward
10.0.2 C:\temp\morefolders Victoria

Which output do you want?


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx