logo
down
shadow

MIN MAX query with a twist


MIN MAX query with a twist

By : dedi halim
Date : November 22 2020, 02:42 PM
Hope that helps The following was written for sql server, but it should work for other databases (sqlfiddle):
code :
with DatesMinMax as
(
    select 
        volume_date, 
        daily_volume, 
        isnull 
        (
            (
            select top 1 d2.volume_date
            from daily_volume  d2
            where d.volume_date > d2.volume_date and d.daily_volume <> d2.daily_volume
            order by d2.volume_date desc
            )
            , '1753-01-01'
        ) as min_date,
        isnull 
        (
            (
            select top 1 d2.volume_date
            from daily_volume  d2
            where d.volume_date < d2.volume_date and d.daily_volume <> d2.daily_volume
            order by d2.volume_date 
            )
            , '9999-12-31'
        )  as max_date
    from daily_volume  d
),
DatesFromTo as 
(
    select d1.daily_volume as qty, 
        (select min(d2.volume_date) 
         from DatesMinMax d2 
         where d2.volume_date > d1.min_date and d2.volume_date < d1.max_date
        ) as [from],
        (select max(d2.volume_date) 
         from DatesMinMax d2 
         where d2.volume_date > d1.min_date and d2.volume_date < d1.max_date
        ) as [to]
    from DatesMinMax d1
)
select distinct
    qty,
    [from],
    [to]
from DatesFromTo
order by [from]


Share : facebook icon twitter icon
MonogDB Query Help - Subobject query w/ a twist

MonogDB Query Help - Subobject query w/ a twist


By : Trending Name
Date : March 29 2020, 07:55 AM
With these it helps With your current schema you can't query like you want(findOne({"quality.owner":"Dan"})). With embedded documents you can search only with dot notation.
But you can redesign your current schema to be like this :
code :
{
  "type":"apple"
  "quality": [
    {
       number: 1,
       "owner":"Dan",
       "age":28    
    },
    {
       number: 2,
       "owner":"Joe",  
       "age":21      
    }]
  }
}
findOne({"quality.owner":"Dan"})
Update Query with a Twist - MYSQL

Update Query with a Twist - MYSQL


By : Grümpy Mellöw
Date : March 29 2020, 07:55 AM
help you fix your problem I have a table with Column name "Height", the table has millions of records , You can do something like :
code :
UPDATE Height SET your_column = REPLACE(your_column, '"', ' inch')
T/SQL - QUERY for duplicate with a twist

T/SQL - QUERY for duplicate with a twist


By : MalaJa
Date : March 29 2020, 07:55 AM
Hope that helps I have the following SQL Code that I have worked on so far. , Something like this perhaps:
code :
;with cte as
(
select t1.id, t1.col1, t1.col2, t2.col3, dense_rank() over(partition by t1.col1, t2.col3 order by t1.col2) DuplicateRowNumber
from @table1 t1 
join @table2 t2 on t1.col2 = t2.col2
)
select *, sign(max(DuplicateRowNumber) over (partition by col1, col3)-1) Ismultiple
from cte
SQL Query to get the top [n] records, with a twist

SQL Query to get the top [n] records, with a twist


By : Lim Kudd
Date : March 29 2020, 07:55 AM
this one helps. One way is to use the window function row_number() and partition by item_cat, item and then use conditional aggregation.
Something like this should work:
code :
WITH cte AS (
    SELECT 
       Item_Cat, Item, City, Qty, 
       rn = ROW_NUMBER() OVER (PARTITION BY Item_Cat, Item ORDER BY Qty DESC) 
    FROM t -- your table
)

SELECT 
    Item_Cat
    , Item
    , Max_City1 = MAX(CASE WHEN rn = 1 THEN City END) 
    , Max_Qty1  = MAX(CASE WHEN rn = 1 THEN Qty  END)
    , Max_City2 = MAX(CASE WHEN rn = 2 THEN City END)
    , Max_Qty2  = MAX(CASE WHEN rn = 2 THEN Qty  END)
FROM cte 
GROUP BY Item_Cat, Item
ORDER BY Item_Cat, Max_qty1 DESC
SQL Query two tables and combine results (with a twist)

SQL Query two tables and combine results (with a twist)


By : Dearmadman
Date : March 29 2020, 07:55 AM
I hope this helps . I have two tables: users and users_info , Te user whit the same parent user should be
code :
SELECT users.*, users_info.* FROM users
INNER JOIN users_info on users_info.parent_id = users.id
where users.id = (SELECT users_info.parent FROM users_info
    WHERE users_info.parent = users.id
    AND users_info.info_val = 'kris@kris.com')
SELECT users.slug,  users.name as name, group_concat(users_info.info_val) FROM users
INNER JOIN users_info on users_info.parent_id = users.id
where users.id = (SELECT users_info.parent FROM users_info
    WHERE users_info.parent = users.id
    AND users_info.info_val = 'kris@kris.com')
 group by users.name
Related Posts Related Posts :
  • Invalid Column Name During SQL Data Read
  • sql program with Group by 2 attributes and 1 condition (Import)
  • Access SQL Select rows that have value in common with results of condition
  • Teradata SQL Performance Tuning: STRTOK_SPLIT_TO_TABLE: EXPLAIN Failed. 3738: String is longer than 31000 characters
  • do i need to rewrite the case statement for every field?
  • How to write dynamic SQL Query to copy items from table with proper MasterLevelId
  • Redshift: Find MAX in list disregarding non-incremental numbers
  • SQL Partition data within and outside of a Common Table Expression
  • PostgreSQL efficient count of number of rows WHERE parameter=TRUE
  • How to select data rows into columns w/out knowing all values
  • Summing a aggregate function
  • SQL query returning values outside the specified range
  • Scripting database SQL Server
  • Delete records and update quantity
  • Trouble executing a function in the where clause if condition is met
  • SQL: Bug in Joining two tables
  • Getting size of users group not working in Ruby
  • How can I iterate through characters in a SQL result value?
  • Write data of one column to unix file with data in another column of the same record as file name
  • How to select the first row of each group?
  • XML Schema totalDigits/fractionDigits vs. SQL precision/scale
  • There is an issue with my code here, can someone look?
  • TSQL turn table rows into columns with column name and value coming from a row
  • join multiple table
  • PHP SQL Select between 4 columns
  • Hierarchical Query / base on table input
  • SQLite regular Expressions regex get exact word by number
  • invalid Column Name
  • Query for last 8 week data
  • Left Join not matching where data exists - Temp Tabls SQL 2012
  • Checking if a value is IN a colon separated column with in a table
  • PostgreSQL constraint error
  • How to use 2 instances of the same table
  • How to format date column to get the day name in Oracle SQL?
  • Run a query based on output of another query
  • How to find particular dates between two rows which have dates in oracle?
  • Oracle SQL: return only max value
  • How to use GROUP BY command properly in Google Big Query?
  • How to echo special characters in batch file?
  • How to read JSON data from SQL Server?
  • Oracle SQL: Use IN operator with inline array
  • Set Hex Value using prepeared stmt in java
  • SonarQube 5.2 Upgrade failed
  • searching for microsoft sql server equivalent of postgresql form
  • How can I write SQL in Oracle in my case?
  • Retrieve records from same name table from multiple databases in single result set
  • Replace duplicate values only in consecutive records with NULL
  • NOT vs <> Operator in sql server
  • Concatenate calculated fields in SQL
  • For each unique value of some groupid column, how do I get rows with last 3 dates?
  • SQL CASE in JOIN returning extra rows
  • Need to find only three different values in multiple columns then sum using Oracle
  • sql - Find the ID of the most booked flight
  • SQL insert data dynamic column name from another table
  • Insert random data in Oracle table
  • Regex to split values in PostgreSQL
  • Which databases are used within a SQL maintenance plan?
  • lag that works for each group separately
  • SparkSQL: conditional sum using two columns
  • SQL Server Maintenance plan history check for success or failure
  • shadow
    Privacy Policy - Terms - Contact Us © animezone.co