logo
down
shadow

Distinct 2 field AS one column SQL SERVER


Distinct 2 field AS one column SQL SERVER

By : Cristian DJ-Xriz
Date : November 19 2020, 12:41 AM
around this issue You can use Union keyword which will give distinct elements from Both Tables
code :
select provfrom  from mytable
union 
select provTo   from mytable


Share : facebook icon twitter icon
HOW TO: SQL Server select distinct field based on max value in other field

HOW TO: SQL Server select distinct field based on max value in other field


By : Rahul Sinha
Date : March 29 2020, 07:55 AM
will be helpful for those in need I've stopped doing things this way since I discovered windowing functions. Too often, there are two records with the same timestamp and I get two records in the resultset. Here's the code for tSQL. Similar for Oracle. I don't think mySQL supports this yet.
code :
Select id, tmpname, date_used, tkt_nbr
From 
(
    Select id, tmpname, date_used, tkt_nbr,
        rownum = Row_Number() Over (Partition by tkt_nbr Order by date_used desc)
) x
Where row_num=1
SQL Server query, get records with distinct value in 1 field and max in other field

SQL Server query, get records with distinct value in 1 field and max in other field


By : samyslimdude
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further I have a table with some columns: , You could try this CTE with the ROW_NUMBER function:
code :
WITH x AS (SELECT t1.param, 
                t1.machine, 
                Cast(t1.val AS FLOAT) AS val, 
                t1.inputdate, 
                RN=Row_number() 
                     OVER( 
                       partition BY t1.machine, t1.param 
                       ORDER BY t1.inputdate DESC) 
         FROM   dbbackend t1 
         WHERE  ( t1.machine LIKE 'MM%' ) 
                AND ( t1.param LIKE 'PAR.SPTDM.%' ) 
                AND ( t1.val <> '' ) 
                AND ( t1.val NOT LIKE '%,%' )) 
SELECT param, 
       machine, 
       val, 
       inputdate 
FROM   x 
WHERE  rn = 1 
Get top 2 rows from each distinct field in a column in Microsoft SQL Server 2008

Get top 2 rows from each distinct field in a column in Microsoft SQL Server 2008


By : Ankit singh
Date : March 29 2020, 07:55 AM
will be helpful for those in need Try to use ROW_NUMBER() function to get N rows per group:
code :
SELECT * 
FROM
  (
     SELECT t.*,
            ROW_NUMBER() OVER (PARTITION BY effectiveDate 
                               ORDER BY empID) 
            as RowNum
     FROM tblHumanResources as t

  ) as t1
WHERE t1.RowNum<=2
ORDER BY effectiveDate
SELECT * 
FROM tblHumanResources as t
WHERE t.EmpID IN (SELECT TOP 2
                         EmpID 
                    FROM tblHumanResources as t2
                   WHERE t2.effectiveDate=t.effectiveDate
                   ORDER BY EmpID)
ORDER BY effectiveDate
SQL Server - Select Distinct of two columns, where the distinct column selected has a maximum value based on two other c

SQL Server - Select Distinct of two columns, where the distinct column selected has a maximum value based on two other c


By : Franco Salonia
Date : March 29 2020, 07:55 AM
To fix this issue I have 2 tables - TC and T, with columns specified below. TC maps to T on column T_ID. , One method uses row_number():
code :
select v_id, tc_id, datetime, count
from (select T.V_ID, TC.TC_ID, T.Datetime, T.Count,
             row_number() over (partition by t.V_ID, tc.tc_id
                                order by datetime desc, count desc
                               ) as seqnum
      from t join
           tc
           on tc.t_id = t._id
     ) tt
where seqnum = 1;
Selecting distinct column of a distinct column using SQL Server

Selecting distinct column of a distinct column using SQL Server


By : SharonYu
Date : March 29 2020, 07:55 AM
I wish did fix the issue. Put the values in the partition by that you want to be unique. So, I think you want userID, house there. The order by doesn't make a difference:
code :
with cte AS (
    select p.* 
           row_number() over (partition by userID, house order by house) AS seqnum
    from people p
)

select userid, house, numofppl, numofcars
from cte 
where seqnum = 1;
Related Posts Related Posts :
  • Trigger on extended properties? SQL SERVER
  • Database Schema guidance for an app store
  • sql trigger to update based on specific column is updated and if value is equal to 'x'
  • How to descope a local variable in t-sql
  • SQL Server Compact stored procedure with multiple values in 1 input
  • How to validate the ICCID number of a SIM card in TSQL?
  • how to calculate all previous datas sum of a sql column on a date range report
  • How to create view listing all friendship for "friend1,friend2" table in T-SQL and prevent inserting duplicate
  • Sum of multiple column in SQL Server
  • Check month and day lies between two dates
  • How would I configure Effort Testing Tool to mock Entity Framework's DbContext withOut the actual SQL Server Database up
  • While looping through a function, Error: Maximum stored procedure, function, trigger, or view nesting level exceeded (li
  • Collapsing records with adjacent start and end dates
  • SQL Server Management Studio 2008 - Timeout Expired error
  • Insert date from VB to SQL Server
  • How to find what table foreign key is from?
  • Sql Server Nested Inserts possible
  • Deterministic way of getting WeekEnding (Sun) for given date column (for use in computed column)
  • SQL Server, why does one order by query work with a string, but not with an integer?
  • How do I install a SQL Server on the hosted build agent in Visual Studio Online?
  • EXECUTE AS/REVERT within a transaction in SQL Server
  • how to create a new database after a trigger fired from a table
  • How to sort string alphabetically
  • Checking data in one column in a temp table to a column in another table
  • What's wrong with my IF/ELSE? "ELSE: Incorrect syntax near 'ELSE'."
  • CASE CLAUSE IN WHERE CONDITION
  • TSQL - Creating a running total of a column
  • Why am I getting not null here?
  • Why does sp_refreshview raise an exception on views that are not schema bound?
  • EXEC parameters from a SELECT
  • SQL Server Compact connection
  • Star Schema Structure - To many Dimensions
  • sort utf string in sql server with join clause
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org