CODEWITHSUNDEEP
sqlsql-servert-sql
Cataster
Cataster

Reputation: 3481

SELECT DISTINCT SELECT other columns

I have a query that fetches system data about a database

$data = Query "SELECT [server_name]
      ,[sessionID]
      ,[user]
      ,[ElapsedTime]
from table"
$data | Export-Csv -Path $Path\data.csv -NoTypeInformation

Recently I've noticed there are duplicate entries for sessionID in the data exported. Is there an option to select distinct by session ID only?

Something like

Query "SELECT [server_name]
      ,SELECT DISTINCT[sessionID]
      ,SELECT [user]
      ,[ElapsedTime]
from table"

For example, if the table has:

table

The exported csv should only contain:

desired table

Upvotes: 2

Views: 377

Answers (2)

Eric Brandt
Eric Brandt

Reputation: 8101

Little too much for a comment.

This query will filter off any rows where all of the values are the same:

$data = Query "SELECT DISTINCT [server_name]
      ,[sessionID]
      ,[user]
      ,[ElapsedTime]
from table"
$data | Export-Csv -Path $Path\data.csv -NoTypeInformation

If any column has a different value, you'll get two (or more) records.

EDIT:

On the other hand, if you just want one record per sessionID, this query will return just that. The ROW_NUMBER() in the ORDER BY requires an internal ORDER BY clause, but it sounds like you don't care what it sorts by, so this is essentially a random ordering to get down to one row.

$data = Query "SELECT TOP (1) WITH TIES [server_name]
      ,[sessionID]
      ,[user]
      ,[ElapsedTime]
from table
ORDER BY ROW_NUMBER() OVER (PARTITION BY sessionID ORDER BY update_time DESC) "
$data | Export-Csv -Path $Path\data.csv -NoTypeInformation

2nd EDIT: Added the update_time to the sort criteria.

Upvotes: 3

Hadi
Hadi

Reputation: 37313

You can use a similar query:

SELECT * FROM (SELECT [server_name]
      ,[sessionID]
      ,[user]
      ,[ElapsedTime]
      ,ROW_NUMBER() OVER(PARITION BY sessionID ORDER BY sessionID) rn
from table) T WHERE rn = 1

OR

WITH CTE_1 AS(SELECT [server_name]
          ,[sessionID]
          ,[user]
          ,[ElapsedTime]
          ,ROW_NUMBER() OVER(PARITION BY sessionID ORDER BY sessionID) rn
    from table) SELECT * FROM CTE_1 WHERE rn = 1

Upvotes: 3

Related Questions