Alternative to ROW_NUMBER() to get row position?
Another approach I've seen people use is the following:
IF (OBJECT_ID('tempdb..#tempresult') IS NOT NULL)
DROP TABLE #tempresult;
CREATE TABLE #tempresult (
idx INT IDENTITY(1,1),
...
);
INSERT #tempresult ...
SELECT ...
FROM ...
JOIN ...
WHERE ...
ORDER BY ...
idx is actually what we look for.
However, not sure if this would be more performance optimal. Depends on your cases.
The temp table could be replaced with table variable if necessary, and also a PRIMARY KEY on idx could be used.
Generally I would always go for ROW_NUMBER() as it is overall the better option.
Alternative to using ROW_NUMBER for better performance
You can try one of the following:
declare @Table table(UpdateID int, LegKey int, OriginalSourceTableID int, UpdateReceived datetime)
Here using the MAX Date in subquery.
select * from @Table as a where a.UpdateReceived = (Select MAX(UpdateReceived) from @Table as b Where b.LegKey = a.LegKey)
Here you can use it in cte with group by.
with MaxDate as( Select LegKey, Max(UpdateReceived) as MaxDate from @Table group by LegKey )
select * from MaxDate as a
inner join @Table as b
on b.LegKey=a.LegKey
and b.UpdateReceived=a.MaxDate
A better alternative to ROW_NUMBER to retrieve a single record per group
This is your query:
SELECT ProductId, @PriceDate AS PriceDate, Price
FROM (SELECT fp.*,
ROW_NUMBER() OVER (PARTITION BY ProductId
ORDER BY COALESCE(FromDate, '19000101') DESC, COALESCE(ToDate, '21000101') ASC) AS RowNumber
FROM FactPrices fp
WHERE (FactPrices.FromDate IS NULL OR FactPrices.FromDate <= @PriceDate) AND
(FactPrices.ToDate IS NULL OR FactPrices.ToDate >= @PriceDate)
) A
WHERE A.RowNumber = 1;
The where clause is a big-time performance killer. I am going to suggest adding two computed columns and then two more indexes.
The computed columns are:
FromDateNotNull as (coalesce(FromDate, '19000101'))
ToDateNotNull as (coalesce(ToDate, '21000101'))
Then, create indexes on:
(Prices, FromDateNotNull desc, ToDateNotNull asc)
(FromDateNotNull, ToDateNotNull)
Then, write the query as:
SELECT ProductId, @PriceDate AS PriceDate, Price
FROM (SELECT fp.*,
ROW_NUMBER() OVER (PARTITION BY ProductId
ORDER BY FromDateNotNull DESC, ToDateNotNull
) AS RowNumber
FROM FactPrices fp
WHERE FromDateNotNull <= @PriceDate AND
ToDateNotNull >= @PriceDate
) A
WHERE A.RowNumber = 1;
Instead of ROW_NUMBER ORDER BY
To recreate the partition by EmpId, your subquery should have l.EmpId = m.Empid. You really need a unique column or set of columns to unique identify a row for this version to work properly. In an attempt based on the given data, if EmpId, Amt are a unique pair you can use and l.Amt < m.Amt. If you have a surrogateid on the table, that would be better instead of Amt.
select
k.id
, k.[Name]
, m.Amt
, ( select count(*)
from EmpIncrement l
where l.Empid = m.Empid
and l.Amt <= m.Amt
) as RowNum
from EmpIncrement m
inner join Employee k
on m.Empid = k.id
If you have no set of columns to uniquely identify and order the rows, you can use a temporary table with an identity() column.
create table #temp (tmpid int identity(1,1) not null, id int, [Name] varchar(32), Amt int);
insert into #temp (id, [Name], Amt);
select
k.id
, k.[Name]
, m.Amt
from EmpIncrement m
inner join Employee k
on m.Empid = k.id;
select
t.id
, t.[Name]
, t.Amt
, ( select count(*)
from #Temp i
where i.Empid = t.Empid
and i.tmpId <= t.tmpId
) as RowNum
from #temp t
How to mimic a ROW_NUMBER() functionality without using ROW_NUMBER()?
This is all kinds of ugly, but with no windowing functions this is the only way I know of to mimic the row_number function:
select
t.Name, t.DateAdded, t.Amount,
( select count (*)
from MyTable u
where
t.Name = u.Name and
t.DateAdded >= u.DateAdded
) as row_number
from MyTable t
Results:
John 2015-10-12 100 1
John 2015-10-15 50 2
Aaron 2015-09-20 10 1
Paul 2014-12-20 24 1
Paul 2014-12-23 32 2
Alternative to ROW_NUMBER() OVER (PARTITION BY x ORDER BY z)
The queries are basically correct.
You problem is the CTE.
A CTE is just syntax.
Those CTEs are evaluated and most likely evaluated more than once.
A second problem is join on two condition is typically not efficient.
Materialize row 1 (and only the row 1's) into #temp tables and even declare a PK
So your join should look more like this
FROM #ActiveProducts
LEFT OUTER JOIN #RawSalesData
ON #RawSalesData.PRO_CODE = #ActiveProducts.PRO_CODE
LEFT OUTER JOIN #RawPurchaseData
ON #RawPurchaseData.PRO_CODE = #ActiveProducts.PRO_CODE
P.S. You can use CTEs to populate the #temp but don't have a CTE in the join of the final query.
you are doing a lot of stuff that I don't think is required
create table #RawSalesData ..
create table #RawPurchaseData ...
WITH RawSalesData
AS ( SELECT Orders.DOC_NUMBER ,
Orders.SLS_CODE ,
Orders.SOH_DATE_ENTER ,
Lines.PRO_CODE ,
Lines.SOL_PRI_TRA ,
ROW_NUMBER() OVER ( PARTITION BY Lines.PRO_CODE
ORDER BY Orders.SOH_DATE_ENTER DESC ) AS SaleRank
FROM tbl_SynergyCache_SOH_HEAD AS Orders WITH ( NOLOCK )
JOIN tbl_SynergyCache_SOL_LINE AS Lines WITH ( NOLOCK )
ON Lines.DEPOT_CODE = Orders.DEPOT_CODE
AND Lines.DOC_ID = Orders.DOC_ID
AND Lines.DOC_NUMBER = Orders.DOC_NUMBER
),
RawPurchaseData
AS ( SELECT Orders.DOC_NUMBER ,
Orders.PUR_CODE ,
Orders.PUR_DATE ,
Lines.PRO_CODE ,
Lines.PUR_PRI_TRA ,
ROW_NUMBER() OVER ( PARTITION BY Lines.PRO_CODE
ORDER BY Orders.PUR_DATE DESC ) AS PurchaseRank
FROM tbl_SynergyCache_PUR_ORD AS Orders WITH ( NOLOCK )
JOIN tbl_SynergyCache_PUR_LINE AS Lines WITH ( NOLOCK )
ON Lines.DEPOT_CODE = Orders.DEPOT_CODE
AND Lines.DOC_ID = Orders.DOC_ID
AND Lines.DOC_NUMBER = Orders.DOC_NUMBER
),
insert into #RawSalesData ...
select ...
from RawSalesData
where RawSalesData.SaleRank = 1
and LTRIM(RTRIM(ISNULL(PRO_CODE, ''))) <> ''
order by RawSalesData.PRO_CODE; -- index this column for an even faster join
insert into #RawPurchaseData ...
select ...
from RawPurchaseData
where RawPurchaseData.PurchaseRank = 1
and LTRIM(RTRIM(ISNULL(PRO_CODE, ''))) <> ''
order by RawPurchaseData.PRO_CODE;;
SELECT #RawSalesData.PRO_CODE AS ProductCode,
#RawSalesData.SOH_DATE_ENTER AS LastSold,
#RawSalesData.DOC_NUMBER AS LastSold_OrderNo,
#RawSalesData.SLS_CODE AS LastSold_AccountNo,
#RawSalesData.SOL_PRI_TRA AS LastSold_Price,
#RawPurchaseData.PUR_DATE AS LastPurchased,
#RawPurchaseData.DOC_NUMBER AS LastPurchased_PurchaseOrderNo,
#RawPurchaseData.PUR_CODE AS LastPurchased_AccountNo,
#RawPurchaseData.PUR_PRI_TRA AS LastPurchased_Price
FROM #RawSalesData
LEFT OUTER JOIN #RawPurchaseData
ON #RawPurchaseData.PRO_CODE = #RawSalesData.PRO_CODE
-- now this join is trivial
ORDER BY #RawSalesData.PRO_CODE
tweak of revised answer
CREATE TABLE #tbl_RawSalesData
(
DOC_NUMBER CHAR(10) ,
SLS_CODE CHAR(20) ,
SOH_DATE_ENTER DATETIME ,
PRO_CODE CHAR(50) PRIMARY KEY ,
SOL_PRI_TRA FLOAT
)
INSERT INTO #tbl_RawSalesData
( DOC_NUMBER ,
SLS_CODE ,
SOH_DATE_ENTER ,
PRO_CODE ,
SOL_PRI_TRA
)
SELECT DOC_NUMBER ,
SLS_CODE ,
SOH_DATE_ENTER ,
PRO_CODE ,
SOL_PRI_TRA
FROM ( SELECT Orders.DOC_NUMBER ,
Orders.SLS_CODE ,
Orders.SOH_DATE_ENTER ,
Lines.PRO_CODE ,
Lines.SOL_PRI_TRA ,
ROW_NUMBER() OVER ( PARTITION BY Lines.PRO_CODE ORDER BY Orders.SOH_DATE_ENTER DESC ) AS SaleRank
FROM tbl_SynergyCache_SOH_HEAD AS Orders WITH ( NOLOCK )
JOIN tbl_SynergyCache_SOL_LINE AS Lines WITH ( NOLOCK ) ON Lines.DEPOT_CODE = Orders.DEPOT_CODE
AND Lines.DOC_ID = Orders.DOC_ID
AND Lines.DOC_NUMBER = Orders.DOC_NUMBER
Where LTRIM(RTRIM(ISNULL(PRO_CODE, ''))) <> ''
) AS RawSalesData
WHERE SaleRank = 1
ORDER BY PRO_CODE
CREATE TABLE #tbl_RawPurchaseData
(
DOC_NUMBER CHAR(10) ,
PUR_CODE CHAR(20) ,
PUR_DATE DATETIME ,
PRO_CODE CHAR(50) PRIMARY KEY ,
PUR_PRI_TRA FLOAT
) INSERT INTO #tbl_RawPurchaseData
( DOC_NUMBER ,
PUR_CODE ,
PUR_DATE ,
PRO_CODE ,
PUR_PRI_TRA
)
SELECT DOC_NUMBER ,
PUR_CODE ,
PUR_DATE ,
PRO_CODE ,
PUR_PRI_TRA
FROM ( SELECT Orders.DOC_NUMBER ,
Orders.PUR_CODE ,
Orders.PUR_DATE ,
Lines.PRO_CODE ,
Lines.PUR_PRI_TRA ,
ROW_NUMBER() OVER ( PARTITION BY Lines.PRO_CODE ORDER BY Orders.PUR_DATE DESC ) AS PurchaseRank
FROM tbl_SynergyCache_PUR_ORD AS Orders WITH ( NOLOCK )
INNER JOIN tbl_SynergyCache_PUR_LINE AS Lines WITH ( NOLOCK ) ON Lines.DEPOT_CODE = Orders.DEPOT_CODE
AND Lines.DOC_ID = Orders.DOC_ID
AND Lines.DOC_NUMBER = Orders.DOC_NUMBER
Where LTRIM(RTRIM(ISNULL(PRO_CODE, ''))) <> ''
) AS RawPurchaseData
WHERE PurchaseRank = 1
ORDER BY PRO_CODE
SELECT isnull (P.PRO_CODE, S.PRO_CODE)
S.SOH_DATE_ENTER ,
S.DOC_NUMBER ,
S.SLS_CODE ,
S.SOL_PRI_TRA ,
P.PUR_DATE ,
P.DOC_NUMBER ,
P.PUR_CODE ,
FROM #tbl_RawPurchaseData AS P
FULL OUTER JOIN #tbl_RawSalesData AS S ON S.PRO_CODE = P.PRO_CODE
DROP TABLE #tbl_ActiveProducts DROP TABLE #tbl_RawPurchaseData DROP TABLE #tbl_RawSalesData
Alternative to row_number to create a unique column in Sybase?
This is what the identity column attribute is for, eg:
CREATE TABLE #my_test
(my_id int identity -- pick a data type that'll be big enough to hold all future values
,my_date datetime
,my_value float NULL
)
go
-- exclude identity column from INSERT (column_list):
INSERT INTO #my_test (my_date, my_value) SELECT '1-Nov-2021',100
INSERT INTO #my_test (my_date, my_value) SELECT '2-Nov-2021',110
INSERT INTO #my_test (my_date, my_value) SELECT '3-Nov-2021',100
INSERT INTO #my_test (my_date, my_value) SELECT '4-Nov-2021',110
INSERT INTO #my_test (my_date, my_value) SELECT '5-Nov-2021',108
INSERT INTO #my_test (my_date, my_value) SELECT '6-Nov-2021',105
go
select * from #my_test
order by my_date
go
my_id my_date my_value
----------- ------------------------------- ---------------------------
1 Nov 1 2021 12:00AM 100.000000
2 Nov 2 2021 12:00AM 110.000000
3 Nov 3 2021 12:00AM 100.000000
4 Nov 4 2021 12:00AM 110.000000
5 Nov 5 2021 12:00AM 108.000000
6 Nov 6 2021 12:00AM 105.000000
To add an identity column for a select into:
select my_id2=identity(int), -- define new identity column name and precision
my_date,
my_value
into #my_test2
from #my_test
order by my_date
go
select * from #my_test2
order by my_date
go
my_id2 my_date my_value
----------- ------------------------------- ---------------------------
1 Nov 1 2021 12:00AM 100.000000
2 Nov 2 2021 12:00AM 110.000000
3 Nov 3 2021 12:00AM 100.000000
4 Nov 4 2021 12:00AM 110.000000
5 Nov 5 2021 12:00AM 108.000000
6 Nov 6 2021 12:00AM 105.000000
NOTES:
- OP will want to read up on identity gaps and identity burn factor
- primary issue is that if/when ASE is shutdown hard, upon starting up you'll see a significant gap between the last identity value and the new identify value
- see
identity_gapsetting forcreate tableandsp_chgattribute(for altering identity attribute of a current table) - for
select/intosee theidentity_gapoption - above code tested in
ASE 16.0 SP04 GA
Related Topics
Sql Take Just the Numeric Values from a Varchar
Multiple Rows Output into Variables in MySQL
Mysql in Xampp Starts and Stops in Five Seconds
Mysql Select Rows on First Occurrence of Each Unique Value
Deleting Rows from Parent and Child Tables
Phpmyadmin Says No Privilege to Create Database, Despite Logged in as Root User
Get Only Records Created Today in Laravel
How to Split a Record Based on Dates in SQL Server
How to Create Column in SQL Query With Custom Text
How to Round an Average to 2 Decimal Places in Postgresql
Multiplying Two Columns in SQL Server
Error:More Than One Row Returned by a Subquery Used as an Expression
Solve Query for Showing Top 5 Selling Products
How to Import a Large (14 Gb) MySQL Dump File into a New MySQL Database