Related questions

SQL Group by Max not desired results

2022-05-04 10:22 Michael imported from Stackoverflow

I'm using SQL Server 2014 and have a table like this:

PK    ContactID  Tele    Mob   Land  Email  Txt
1       10        1       0     1     1      1
2       10        0       1     0     0      1
3       12        1       3     1     1      1

The desired result is:

PK    ContactID  Tele    Mob   Land  Email  Txt
2       10        0       1     0     0      1
3       12        1       3     1     1      1

However, if I perform a GroupBy \ Max:

SELECT        MAX(PK) AS PK, ContactID, Tele, Mob, Land, Email, Txt
FROM            Contacts
GROUP BY ContactID, Tele, Mob, Land, Email, Txt

I'm just receiving:

PK    ContactID  Tele    Mob   Land  Email  Txt
1       10        1       0     1     1      1
2       10        0       1     0     0      1
3       12        1       3     1     1      1

How do I modify to give me the desired results?

2 answers

  • answered 2022-05-04 10:28 D-Shih

    If I understand correctly, you can try to use ROW_NUMBER window function to make it.

    SELECT *
FROM (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY ContactID ORDER BY PK DESC) rn
    FROM Contacts
) t1
WHERE rn = 1

  • answered 2022-05-04 10:36 Serg

    The last by PK row for a ContactId using top with ties

    SELECT top(1) with ties *
FROM Contacts
ORDER BY row_number() over(partition by ContactID order by PK desc)

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum

See also questions close to this topic

  • Why does this Sql query shows the possible changes but does not implement it?

    So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it.

    SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO fan_', SUBSTRING_INDEX(TABLE_NAME, 'pc_',-1), ';') FROM information_schema.tables WHERE table_name like 'pc_%' and table_schema='testdbhere'

    Moreover, this isn't a writing privilege issue as changing the tables name individually works perfectly from the same user.

  • how to get weeklytotal and yesterday record in mysql in one table

    Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.

    Table name-dailydata-

    Sample data

    employee_id date total
    20 2022-04-25 10
    20 2022-04-26 20
    20 2022-04-27 20
    20 2022-04-28 10
    20 2022-04-29 20
    20 2022-04-30 30
    20 2022-04-31 40
    20 2022-05-01 50
    40 2022-04-26 20

    expected output

    employee_id weekly_total yesterday_record
    20 200 40
    40 20 0

    mysql query to get weekly data

    select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'
  • Procedure to display the Employees of a specific Department

    Create a stored procedure, to display the lastnames as "Name" from the employee table. The requirement is as below: The name of the Procedure : EmployeesDept

    Name of the argument as Input : DeptNo

    Write the code to create the procedure

    Schema

    I wrote the Query like this but i didnt get expected output

    CREATE  PROCEDURE Employeesdept(@Deptno varchar)
AS
Begin
SELECT lastname as name
from employee
where workdept= 'D21'
end

    Expected output:

    Name --------------- Pulaski Jefferson Marino Smith Johnson Perez Monteverde Name ---------------

  • Tables won't appear as selectable object after creating it 
    SELECT *
FROM EmployeeDemographics


Msg 208, Level 16, State 1, Line 2

Invalid object name 'EmployeeDemographics'.

    I'm currenlty watching Alex the Analyst's SQL tutorial and everytime I create a table, close, and restart SQL, the table is not found by SQL. When I use a statement such as FROM the table doesn't appear in the list of selectable objects. When I type it manually, the table_name has red lines under it indicating that it's an invalid object name. When I run the code I get the error message above. I tried refreshing the database and the Intellisense to no avail. Let me know if I should post the code for the table and thanks in advanced.

  • count how many people who buy the same item at second time and how many people who did not buy the second item at second time (compare to firsttime)

    Here is the question:count how many people who buy the same item at second time and how many people who did not buy the second item at second time (compare to buying_order = 1)

    We have a table:

    id item date buying_order
    1 1 19990101 1
    1 1 19990102 2
    2 1 19990102 1
    2 2 19990101 2
    2 3 19990103 1

    To be more clear, if id 1 buy item 1 on 19990101, that is the first purchased item so the buying_order is 1. Our concern is know how many people buy a specific item at their first time, and how many people re-purchase that specific item at the second time.

    Here are the logic I have tried, but not work

    SELECT a.id FROM (SELECT id FROM table WHERE buying_order = 1) AS a 
LEFT JOIN table AS b ON a.id = b.id 
WHERE b.buying_order=2 and a.item = b.item

    Expected result:

    item first_purchase_customer second_purchase second_buy_other_item_count
    1 2 1 1

    For item 1, there are two first purchase at order 1, and there is only one customer buy item 1 at order 2.

    NOTE: The order can be higher than 2, such that order = 3,4,..., but we only care the people who buy or not buy the same item at their second purchase.

  • Total up transactions

    I am using SQL Server Management Studio v18.

    I have an original transaction that might get adjusted either by amount or by commission%.

    I want a query to total up the transactions based on ID and company and I want to keep the original transaction's TransType.

    I have full understanding on how to aggregate columns but I don't know how to keep the TransType.

    Original table:

    ID TransType Amount Commission Commission% Company Notes
    555 RE 500 125 25% Company A Original Transaction
    555 RT -400 -100 0% Company A Adjustment
    666 RA 500 125 25% Company A Original Transaction
    666 AD 0 -100 -20% Company A Adjustment
    666 RN 200 40 20% Company B Original Transaction

    Final result:

    ID TransType Amount Commission Commission% Company
    555 RE 100 25 25% Company A
    666 RA 500 25 5% Company A
    666 RN 200 40 20% Company B
  • How to select distinct value

    I need to select all supervisor records. Our HR employee table set up like below

    Firstname Lastname Email Supervisor
    Frank Johns fjohns Taylor, Don
    Pat Hope phope Taylor, Don
    Jen Dow jdow Taylor, Don
    Taylor Don tdon Olson, Mike
    Kim Ronda kronda Olson, Mike
    Rob Smith rsmith Olson, Mike
    Mike Olson molson null

    The final result should be like this

    Firstname Lastname Email Supervisor
    Taylor Don tdon Olson, Mike
    Mike Olson molson null

    If I use Distinct on supervisor then it gives me a list of supervisor but not their info. Please help

  • SQL Join tables - join with last four digits of columns

    I'm trying to join two tables in SQL. I want to join on the column with the last four digits of a phone number with the a column from a second column where only the last four digits are shown.

    From ExcelSheet1 E
Join Walkers W
ON W.Lastname = E.LNAME
AND W.Firstname = E.FNAME
and W.PhoneNum like '%'+E.MEMBER_NUM
  • Can't group employees list without years

    my issue is that when I want to group a set of employees according to the month of their birth date, Odoo separates the months by years, which I want to avoid because i wanna generate a list of people who have their birthday in the month.

    This is code:

    <filter
    name="birthday_groupby"
    string="Birthday month"
    domain="[]"
    context="{'group_by': 'birthday:week'}"
/>

    And this is result:

    List screenshot

    As you can see, it separates by months, but also by years. I think solution may be more easier than than I think, but i can't figure it out.

  • Fetching values from one column based on other column keys in long-formatted dataset

    I have a long format dataset of 100,000+ individuals, capturing clinic visits at 5 different time points (not chronological). I've included an example dataset below that replicates the formatting of my data:

    • ID: participant ID visit_number: order of the clinic visits in the original dataset

    • age_visit: age at the time of the visit

    • clinic_number: the identifier for the specific clinic location

    • age_sorted: For each ID, age sorted in ascending order across the 5 clinic visits

    • age_sorted_index: For each ID, the visit number corresponding to the sorted age

    I would like to create a new column (clinic_number_extracted) that fetches the clinic identifier (clinic_number) corresponding to each sorted age (age_sorted value) for each participant. I was thinking that it might be possible to use the age_sorted_index and visit_number variables to do so (generating key-value pairs?), but am not quite sure how to do this outside of data.table. A tidyverse solution would be preferred.

    I've looked on R community and stack exchange for clues, but haven't been able to find exactly what I'm looking for (likely not using the correct search terms). I tried to play around with group_by(across()) and with_order(order_by()) functions without much success. I can potentially create a new variable with a few case_when() conditions but might run into issues if there are repeated age_assessment_sorted values.

    set.seed(42)

# Beginning dataset
das <- data.frame(id = rep(letters[1:3], each = 5), 
                  visit_number = rep(1:5, times = 3),
                  age_visit = c(50, rep(NA_real_, times = 7), 34, 40, 72, rep(NA_real_, times = 3), 87), 
                  clinic_number = sample(30:50, 15, replace=TRUE), 
                  age_sorted = c(50, rep(NA_real_, times = 4), 34, 40,rep(NA_real_, times = 3), 72, 87, rep(NA_real_, times = 3)), 
                  age_sorted_index = c(rep(1:5), 4, 5, rep(1:3), 1, 5, 2, 3, 4)) 

# Print out dataset
das
#>    id visit_number age_visit clinic_number age_sorted age_sorted_index
#> 1   a            1        50            46         50                1
#> 2   a            2        NA            34         NA                2
#> 3   a            3        NA            30         NA                3
#> 4   a            4        NA            39         NA                4
#> 5   a            5        NA            33         NA                5
#> 6   b            1        NA            47         34                4
#> 7   b            2        NA            46         40                5
#> 8   b            3        NA            44         NA                1
#> 9   b            4        34            36         NA                2
#> 10  b            5        40            33         NA                3
#> 11  c            1        72            34         72                1
#> 12  c            2        NA            43         87                5
#> 13  c            3        NA            49         NA                2
#> 14  c            4        NA            47         NA                3
#> 15  c            5        87            44         NA                4

    Desired data:

    das_final <- cbind(das, 
                  clinic_number_extracted = c(46, rep(NA_real_, times = 4), 36, 33, rep(NA_real_, times = 3), 34, 44, rep(NA_real_, times = 3)))

# Print out final dataset
das_final 
#>    id visit_number age_visit clinic_number age_sorted age_sorted_index
#> 1   a            1        50            46         50                1
#> 2   a            2        NA            34         NA                2
#> 3   a            3        NA            30         NA                3
#> 4   a            4        NA            39         NA                4
#> 5   a            5        NA            33         NA                5
#> 6   b            1        NA            47         34                4
#> 7   b            2        NA            46         40                5
#> 8   b            3        NA            44         NA                1
#> 9   b            4        34            36         NA                2
#> 10  b            5        40            33         NA                3
#> 11  c            1        72            34         72                1
#> 12  c            2        NA            43         87                5
#> 13  c            3        NA            49         NA                2
#> 14  c            4        NA            47         NA                3
#> 15  c            5        87            44         NA                4
#>    clinic_number_extracted
#> 1                       46
#> 2                       NA
#> 3                       NA
#> 4                       NA
#> 5                       NA
#> 6                       36
#> 7                       33
#> 8                       NA
#> 9                       NA
#> 10                      NA
#> 11                      34
#> 12                      44
#> 13                      NA
#> 14                      NA
#> 15                      NA

    Created on 2022-05-06 by the reprex package (v2.0.1)

  • Group unique values in r

    I have a following data:

    I want o summarise the data in three columns by Subject type and other two columns sates number of schools and number of students by each subject type. I have used group_by function by it keeps giving the number of types subjects appeared rather then by the number of schools or students

    TIA