SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with SQL Query


Help with SQL Query

Author
Message
manssourianm
manssourianm
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 118
Hi, is it possible to do the following?
I have this table:


CREATE TABLE test1(
[A] [int] NULL,
[B] [varchar](10) NULL
)



It has this data in it:


insert into test1 (a, b)
values (1, 'A')
insert into test1 (a, b)
values (2, null)
insert into test1 (a, b)
values (3, null)
insert into test1 (a, b)
values (4, null)
insert into test1 (a, b)
values (5, 'B')
insert into test1 (a, b)
values (6, null)
insert into test1 (a, b)
values (7, null)
insert into test1 (a, b)
values (8, 'C')
insert into test1 (a, b)
values (9, null)
insert into test1 (a, b)
values (10, null)
insert into test1 (a, b)
values (11, null)
insert into test1 (a, b)
values (12, null)
insert into test1 (a, b)
values (13, null)
insert into test1 (a, b)
values (14, 'D')
insert into test1 (a, b)
values (15, null)
insert into test1 (a, b)
values (16, null)
insert into test1 (a, b)
values (17, null)
insert into test1 (a, b)
values (18, null)
insert into test1 (a, b)
values (19, 'E')



I would like to write a query that can produce these results:


insert into test1 (a, b)
values (1, 'A')
insert into test1 (a, b)
values (2, 'A')
insert into test1 (a, b)
values (3, 'A')
insert into test1 (a, b)
values (4, 'A')
insert into test1 (a, b)
values (5, 'B')
insert into test1 (a, b)
values (6, 'B')
insert into test1 (a, b)
values (7, 'B')
insert into test1 (a, b)
values (8, 'C')
insert into test1 (a, b)
values (9, 'C')
insert into test1 (a, b)
values (10, 'C')
insert into test1 (a, b)
values (11, 'C')
insert into test1 (a, b)
values (12, 'C')
insert into test1 (a, b)
values (13, 'C')
insert into test1 (a, b)
values (14, 'D')
insert into test1 (a, b)
values (15, 'D')
insert into test1 (a, b)
values (16, 'D')
insert into test1 (a, b)
values (17, 'D')
insert into test1 (a, b)
values (18, 'D')
insert into test1 (a, b)
values (19, 'E')



If this can be done, any help is appreciated!
Thanks.
sgmunson
sgmunson
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7205 Visits: 4382
I have a working solution, but before I present it, you need to know that what you're asking for looks like a homework assignment rather than a real-world business problem. We don't do your homework for you. Yes, a query can be written to do this. What have you tried so far? Are you thinking in terms of what you can do to a column (set-based thinking), as opposed to using procedural thinking?

What can you tell us that you've noticed about the data itself? Might there be a useful subset of this data that could help be a part of the solution? I don't mind helping lead you down the path toward a solution, but YOU have to contribute to the process.

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
manssourianm
manssourianm
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 118
Hi Steve, thanks for your response.
This is totally a work problem, the example I provided is a very simplified version of our actual data. To give you the background: I work for a very large transit agency. In our data, our bus stops are numbered sequentially 1 to n (column 'a'). Every ten or so bus stops, we have a TimePoint, the Timepoint field is null everywhere else (column 'b'). One of our managers wants to know, from one timepoint to another, what is the maximum passenger load, and what the preceding timepoint was. In the current state of our data, I can only find the passenger load AT THE STOP that is a timepoint.
So, if I was able to fill in the nulls with what timepoint those sequence numbers actually belong to, I can then group by that field and take the max(passengerload). I couldn't think of another way of doing this.
Thanks again!
sgmunson
sgmunson
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7205 Visits: 4382
Okay... Now we have something better to work with. The query below includes your original example code, but modifies the table to have a clustered index based on "A". It might well suffer performance-wise because of the nature of the JOIN, which uses a sub-query. This can be dangerous when the number of records is large, so you'll want to possibly create a SPARSE index on "B", WHERE B IS NOT NULL.

Here's the SQL:


DECLARE @TEST1 TABLE (
A int PRIMARY KEY CLUSTERED,
B varchar(10) NULL
)

insert into @TEST1 (A, B) values (1, 'A')
insert into @TEST1 (A, B) values (2, null)
insert into @TEST1 (A, B) values (3, null)
insert into @TEST1 (A, B) values (4, null)
insert into @TEST1 (A, B) values (5, 'B')
insert into @TEST1 (A, B) values (6, null)
insert into @TEST1 (A, B) values (7, null)
insert into @TEST1 (A, B) values (8, 'C')
insert into @TEST1 (A, B) values (9, null)
insert into @TEST1 (A, B) values (10, null)
insert into @TEST1 (A, B) values (11, null)
insert into @TEST1 (A, B) values (12, null)
insert into @TEST1 (A, B) values (13, null)
insert into @TEST1 (A, B) values (14, 'D')
insert into @TEST1 (A, B) values (15, null)
insert into @TEST1 (A, B) values (16, null)
insert into @TEST1 (A, B) values (17, null)
insert into @TEST1 (A, B) values (18, null)
insert into @TEST1 (A, B) values (19, 'E')

;WITH NOT_NULL AS (

SELECT A, B
FROM @TEST1
WHERE B IS NOT NULL
)
SELECT T.A, T2.B
FROM @TEST1 AS T
INNER JOIN NOT_NULL AS T2
ON T2.A = (SELECT MAX(A) FROM NOT_NULL WHERE A <= T.A)
ORDER BY T.A;



Let me know what you discover with this.

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
sgmunson
sgmunson
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7205 Visits: 4382
Just uploading the execution plan so you can see what you might be in for. The estimates aren't very good on number of executions, so the potential for ugly is present.

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Attachments
TEST1.sqlplan (3 views, 12.00 KB)
manssourianm
manssourianm
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 118
Thank you very much for your efforts, I learned a new thing today.
I'm still trying to wrap my head around the join that you did. Very clever...
Now it will take some time to adapt this into the assignment, that should be the fun part!
sgmunson
sgmunson
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7205 Visits: 4382
Don't get too attached to the idea. It's more of a quick fix, and could be problematic in terms of applying it to a larger set of data, although there is some potential for it doing well if any given route is small in terms of total number of stops. You'll want to test this to be sure you aren't creating a peformance nightmare.

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search