logo
down
shadow

how to use rank/join and where together


how to use rank/join and where together

By : helio
Date : January 12 2021, 07:00 PM
Does that help You could either do sub-query or CTE, i would suggest try with 2 methods and look at execution plan pick which performs better:
Sub Query
code :
SELECT * FROM 
(select [YEAR],
       [IDManufacturer],
       sum([TotalPrice]) TotalPrice, 
       rank() over (order by sum(totalprice) desc) as sales_rank
from [dbo].[DIM_DATE] 
       join [dbo].[FACT_TRANSACTIONS] 
       on [dbo].[FACT_TRANSACTIONS].Date = [dbo].[DIM_DATE].DATE
       join [dbo].[DIM_MODEL] 
       on [dbo].[DIM_MODEL].IDModel=[dbo].[FACT_TRANSACTIONS].IDModel
where [YEAR] in (2009,2010) 
       group by IDManufacturer,[year]
) as SQ
Where sales_rank = 3 or sales_rank = 4
go 
; with CTE as

(select [YEAR],
       [IDManufacturer],
       sum([TotalPrice]) TotalPrice, 
       rank() over (order by sum(totalprice) desc) as sales_rank
from [dbo].[DIM_DATE] 
       join [dbo].[FACT_TRANSACTIONS] 
       on [dbo].[FACT_TRANSACTIONS].Date = [dbo].[DIM_DATE].DATE
       join [dbo].[DIM_MODEL] 
       on [dbo].[DIM_MODEL].IDModel=[dbo].[FACT_TRANSACTIONS].IDModel
where [YEAR] in (2009,2010) 
       group by IDManufacturer,[year]
)

SELECT * FROM CTE WHERE sales_rank = 3 or sales_rank = 4



Share : facebook icon twitter icon
How do I use rank() with an inner join?

How do I use rank() with an inner join?


By : kabesan
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , I want to join my CHEESE table with FRESHNESS to get CHEESE and FRESHNESS code for cheeses where max(seq_no) for each cheese id is MOLD. , You can either do the join in the subquery
code :
select cheese_id,seq_no,fresh_code,fresh_desc,seq_rank
  from ( select cheese_id,
                seq_no, 
                fresh_code, 
                fresh_desc,
                rank() over (partition by cheese_id
                                 order by seq_no desc) seq_rank
          from cheese
               join freshness using (fresh_code) )
 where seq_rank = 1
   and fresh_code = 'MOLD'
select cheese_id,seq_no,fresh_code,fresh_desc,seq_rank
  from ( select cheese_id,
                seq_no, 
                fresh_code, 
                fresh_desc,
                rank() over (partition by cheese_id
                                 order by seq_no desc) seq_rank
          from cheese ) cheese_outer
       join freshness using (fresh_code)
 where seq_rank = 1
   and fresh_code = 'MOLD'
distinct + rank over join

distinct + rank over join


By : gmedel
Date : March 29 2020, 07:55 AM
With these it helps lets say we have a table BNK_ACCT_GRP_ST with these columns: , Try this:
code :
WITH BankData AS
(
    SELECT  a.*, 
            ROW_NUMBER()
              OVER(PARTITION BY fncl_grp_id, bnk_acct_id ORDER BY bnk_acct_grp_id DESC) AS Position
      FROM bnk_acct_grp_st a
)
SELECT  *
  FROM  BankData
 WHERE  Position = 1
pig order by with rank and join the rank together

pig order by with rank and join the rank together


By : Ariels
Date : March 29 2020, 07:55 AM
This might help you I have the following data with the schema (t0:chararray, t1:int) , There is the RANK operation in Pig. This should be sufficient:
code :
X = rank A by t1 ASC;
rank on mysql with join

rank on mysql with join


By : Sameer Singh
Date : March 29 2020, 07:55 AM
it should still fix some issue I see the problem. Your subquery is saying a.Rata. This refers to the outer table. I suspect that the GROUP_CONCAT() is then confusing MySQL, so the subquery is not interpreted as an aggregation query.
However, you should move the subquery to the FROM clause:
code :
SELECT a.*,
       FIND_IN_SET( a.Rata, l.list) AS rank
from datanilaiujian a JOIN
     datauser b
     ON a.UserID = b.UserID CROSS JOIN
     (SELECT GROUP_CONCAT( a2.Rata ORDER BY a2.Rata DESC ) as list
      FROM datanilaiujian a2
     ) l
ORDER BY a.Rata DESC;
MAX with Inner join VS rank() over

MAX with Inner join VS rank() over


By : Erika Febriyani Yek
Date : March 29 2020, 07:55 AM
Hope this helps You could also use the last function:
Related Posts Related Posts :
  • Date Condition with Case when Holiday exclude
  • SQL Query Select from 1 table and return data based on 2 columns
  • How to create new column with values counting up every 9th value with SQL?
  • More Elegant way to Batch Validate Phone Numbers
  • SQL find parent where children match over multiple rows
  • A combo box that has to be filter the same data in that field
  • Exclude updated record from trigger function
  • Insert into table from select only when select returns valid rows
  • How to fix SQLite3::SQLException: near "to": syntax error: SELECT "".* FROM "" WHERE "
  • Filter msdb.dbo.sysjobsteps.command into seperate columns
  • How to extract year or month,day from oracle function without using sub-query?
  • Reset sum when condition is met in Oracle
  • Date Between (Start & Now)
  • Reset running sum when condition is met in Oracle
  • Why is my date criteria filtering incorrectly in a MsysObjects query using DCount?
  • Stripping date/time delimiters in an SSIS csv export
  • SSIS Error on insertion - Error when new primary keys in Dimension table
  • How can I make a temp table of specific datetime data within a range?
  • AnalysisException: Syntax error in SQL After multiple join
  • SQL Query Multiple Columns Using Distinct on One Column Only and Using Order By
  • Sum inventory item groups
  • TSQL: Parsing strings with various characters
  • Using SQL Server : how to use select criteria based on sum
  • Inserting dynamic pivot Result into Temp table
  • Joining multiple fields in a dataset joined with descriptions
  • Conditional WHERE EXISTS in Oracle query
  • How to get the count of each unique value in a column between two dates?
  • SQL: Select only if multiple values are IN 1 column from CatalogTable
  • In a query (no editing of tables) how do I join data without any similarities?
  • Create nested json blobs in PostgreSQL
  • Getting extra dates while using cte
  • Way to alter one table if another was altered
  • Proper use of semicolons in functions and procedures
  • Cursor Not looping all the Records
  • Select inner join returns wrong result
  • Group By showing duplicates
  • Error in using subquery in access join query
  • How do I use a composite pk in a stored procedure?
  • How to convert decode function in oracle to standard big query
  • Postgresql: An alternative to subqueries to make the query more efficient?
  • Use returned values from a query in the same procedure
  • Sum all numeric columns in database and log results
  • Release item in current year
  • Conditional function not working as intended postgresql
  • SQL trigger, results not Grouped by
  • getting more records than expected with simple query in postgresql
  • get first occurrence of a certain combination of columns
  • Converting string timestamp to local-time zone in Oracle
  • Redshift - Filter out rows that are blank to left or right of a character
  • Teradata - Know the load volume by times of a table
  • How do I replace duplicate data with random data of same length in SQL
  • How to group by using pivot table
  • How can I achieve a Join for merging two tables FULL OUTER JOIN and avoid duplicates?
  • Multi Keyword search on multi-column sql
  • Computing the median of salaries under each manager in BigQuery SQL
  • How can insert value by selecting from another
  • Retrieving only the latest item for a set of data
  • update column with concatenating for that group only
  • SQL `SELECT 'some string value' AS fieldname` equivalent in Powershell?
  • How to determine the AVERAGE number of associated records
  • shadow
    Privacy Policy - Terms - Contact Us © festivalmusicasacra.org