Query from Postgres to SQL ServerOuter Apply vs Left Join PerformanceConverting outer apply with Correlated...
How long has this character been impersonating a Starfleet Officer?
If I tried and failed to start my own business, how do I apply for a job without job experience?
How do I add a strong "onion flavor" to the biryani (in restaurant style)?
Buying a "Used" Router
How to change a .eps figure to standalone class?
Equivalent of "illegal" for violating civil law
How to fly a direct entry holding pattern when approaching from an awkward angle?
Is `Object` a function in javascript?
Co-worker sabotaging/undoing my work (software development)
What would be some possible ways of escaping higher gravity planets?
How to politely refuse in-office gym instructor for steroids and protein
Besides PR credit, does diversity provide anything that meritocracy does not?
Is the fingering of thirds flexible or do I have to follow the rules?
RS485 using USART or UART port on STM32
Writing dialogues for characters whose first language is not English
How to deal with an underperforming subordinate?
Plausible reason for gold-digging ant
Identical projects by students at two different colleges: still plagiarism?
Website seeing facebook data from another site?
Why is "rm -r" unable to delete this folder?
When using Volatility with a memory image, what is the Kernel version?
Critique vs nitpicking
Is it really OK to use "because of"?
How can find the 2D Voronoi cell area distribution?
Query from Postgres to SQL Server
Outer Apply vs Left Join PerformanceConverting outer apply with Correlated subquery in SQL Server to SQLiteLEFT OUTER JOIN versus WHERE IS NULLQuery plan missing ParameterCompiledValueConverting old SQL 2000 Non-Ansi code to SQL 2012 compatibleWhere do this Constant Scan and Left Outer Join come from in a trivial SELECT query plan?Can I make this multiple join query faster?Performance gap between WHERE IN (1,2,3,4) vs IN (select * from STRING_SPLIT('1,2,3,4',','))changing from inner joins to left joins to include null values?SQL Server Parallelism Disabled
1
I have this query in PostgreSQL and I am not familiar with PostgreSQL. I need to convert it to SQL Server.
SELECT (applicant_majors.*)::applicant_majors
FROM applicant_majors
WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id
ORDER BY id
LIMIT 1 OFFSET 1
This is the whole query
FROM (SELECT college_attendeds,
colleges,
applicant_degree_1,
applicant_degree_2,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id ORDER BY id LIMIT 1 ) AS applicant_degree_1_applicant_major_1,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id ORDER BY id LIMIT 1 OFFSET 1) AS applicant_degree_1_applicant_major_2,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_1).id ORDER BY minor_number LIMIT 1 ) AS applicant_degree_1_applicant_minor_1,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_1).id ORDER BY minor_number LIMIT 1 OFFSET 1) AS applicant_degree_1_applicant_minor_2,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_2).id ORDER BY id LIMIT 1 ) AS applicant_degree_2_applicant_major_1,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_2).id ORDER BY id LIMIT 1 OFFSET 1) AS applicant_degree_2_applicant_major_2,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_2).id ORDER BY minor_number LIMIT 1 ) AS applicant_degree_2_applicant_minor_1,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_2).id ORDER BY minor_number LIMIT 1 OFFSET 1) AS applicant_degree_2_applicant_minor_2
FROM (SELECT (college_attendeds.*)::college_attendeds AS college_attendeds,(colleges.*)::college_bases AS colleges,
(SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1 ) AS applicant_degree_1,
(SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1 OFFSET 1) AS applicant_degree_2
FROM college_attendeds
LEFT OUTER JOIN college_bases AS colleges ON (college_attendeds).college_id = colleges.id AND colleges.type = 'College') AS applicant_degree_data) AS college_attended_data
LEFT OUTER JOIN degree_bases AS applicant_degree_1_degree ON (applicant_degree_1).degree_id = applicant_degree_1_degree.id AND applicant_degree_1_degree.type IN ('Degree', 'PtDegree')
LEFT OUTER JOIN degree_bases AS applicant_degree_2_degree ON (applicant_degree_2).degree_id = applicant_degree_2_degree.id AND applicant_degree_2_degree.type IN ('Degree', 'PtDegree');
sql-server
edited Jan 21 at 21:10
a_horse_with_no_name
40.1k776112
asked Aug 3 '16 at 19:05
sebeidsebeid
6721817
bumped to the homepage by Community♦ 26 secs ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
1
I have this query in PostgreSQL and I am not familiar with PostgreSQL. I need to convert it to SQL Server.
SELECT (applicant_majors.*)::applicant_majors
FROM applicant_majors
WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id
ORDER BY id
LIMIT 1 OFFSET 1
This is the whole query
FROM (SELECT college_attendeds,
colleges,
applicant_degree_1,
applicant_degree_2,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id ORDER BY id LIMIT 1 ) AS applicant_degree_1_applicant_major_1,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id ORDER BY id LIMIT 1 OFFSET 1) AS applicant_degree_1_applicant_major_2,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_1).id ORDER BY minor_number LIMIT 1 ) AS applicant_degree_1_applicant_minor_1,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_1).id ORDER BY minor_number LIMIT 1 OFFSET 1) AS applicant_degree_1_applicant_minor_2,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_2).id ORDER BY id LIMIT 1 ) AS applicant_degree_2_applicant_major_1,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_2).id ORDER BY id LIMIT 1 OFFSET 1) AS applicant_degree_2_applicant_major_2,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_2).id ORDER BY minor_number LIMIT 1 ) AS applicant_degree_2_applicant_minor_1,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_2).id ORDER BY minor_number LIMIT 1 OFFSET 1) AS applicant_degree_2_applicant_minor_2
FROM (SELECT (college_attendeds.*)::college_attendeds AS college_attendeds,(colleges.*)::college_bases AS colleges,
(SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1 ) AS applicant_degree_1,
(SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1 OFFSET 1) AS applicant_degree_2
FROM college_attendeds
LEFT OUTER JOIN college_bases AS colleges ON (college_attendeds).college_id = colleges.id AND colleges.type = 'College') AS applicant_degree_data) AS college_attended_data
LEFT OUTER JOIN degree_bases AS applicant_degree_1_degree ON (applicant_degree_1).degree_id = applicant_degree_1_degree.id AND applicant_degree_1_degree.type IN ('Degree', 'PtDegree')
LEFT OUTER JOIN degree_bases AS applicant_degree_2_degree ON (applicant_degree_2).degree_id = applicant_degree_2_degree.id AND applicant_degree_2_degree.type IN ('Degree', 'PtDegree');
sql-server
edited Jan 21 at 21:10
a_horse_with_no_name
40.1k776112
asked Aug 3 '16 at 19:05
sebeidsebeid
6721817
bumped to the homepage by Community♦ 26 secs ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
What isapplicant_degree_1?
– ypercubeᵀᴹ
Aug 3 '16 at 19:09
2
The cast(applicant_majors.*)::applicant_majorsis pretty much useless and a complicated way of writing*. The(applicant_degree_1).idlooks pretty strange. That would only be valid ifapplicant_degree_1was a composite type - which is not available in SQL Server.
– a_horse_with_no_name
Aug 3 '16 at 19:10
update the question with the whole query to include applicant_degree_1
– sebeid
Aug 3 '16 at 19:19
That doesn't look like a whole query. No query starts with aFROM.
– ypercubeᵀᴹ
Aug 3 '16 at 19:47
i didn't add the selected fields .
– sebeid
Aug 3 '16 at 19:48
add a comment |
1
1
1
I have this query in PostgreSQL and I am not familiar with PostgreSQL. I need to convert it to SQL Server.
SELECT (applicant_majors.*)::applicant_majors
FROM applicant_majors
WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id
ORDER BY id
LIMIT 1 OFFSET 1
This is the whole query
FROM (SELECT college_attendeds,
colleges,
applicant_degree_1,
applicant_degree_2,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id ORDER BY id LIMIT 1 ) AS applicant_degree_1_applicant_major_1,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id ORDER BY id LIMIT 1 OFFSET 1) AS applicant_degree_1_applicant_major_2,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_1).id ORDER BY minor_number LIMIT 1 ) AS applicant_degree_1_applicant_minor_1,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_1).id ORDER BY minor_number LIMIT 1 OFFSET 1) AS applicant_degree_1_applicant_minor_2,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_2).id ORDER BY id LIMIT 1 ) AS applicant_degree_2_applicant_major_1,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_2).id ORDER BY id LIMIT 1 OFFSET 1) AS applicant_degree_2_applicant_major_2,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_2).id ORDER BY minor_number LIMIT 1 ) AS applicant_degree_2_applicant_minor_1,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_2).id ORDER BY minor_number LIMIT 1 OFFSET 1) AS applicant_degree_2_applicant_minor_2
FROM (SELECT (college_attendeds.*)::college_attendeds AS college_attendeds,(colleges.*)::college_bases AS colleges,
(SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1 ) AS applicant_degree_1,
(SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1 OFFSET 1) AS applicant_degree_2
FROM college_attendeds
LEFT OUTER JOIN college_bases AS colleges ON (college_attendeds).college_id = colleges.id AND colleges.type = 'College') AS applicant_degree_data) AS college_attended_data
LEFT OUTER JOIN degree_bases AS applicant_degree_1_degree ON (applicant_degree_1).degree_id = applicant_degree_1_degree.id AND applicant_degree_1_degree.type IN ('Degree', 'PtDegree')
LEFT OUTER JOIN degree_bases AS applicant_degree_2_degree ON (applicant_degree_2).degree_id = applicant_degree_2_degree.id AND applicant_degree_2_degree.type IN ('Degree', 'PtDegree');
sql-server
edited Jan 21 at 21:10
a_horse_with_no_name
40.1k776112
asked Aug 3 '16 at 19:05
sebeidsebeid
6721817
I have this query in PostgreSQL and I am not familiar with PostgreSQL. I need to convert it to SQL Server.
SELECT (applicant_majors.*)::applicant_majors
FROM applicant_majors
WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id
ORDER BY id
LIMIT 1 OFFSET 1
This is the whole query
FROM (SELECT college_attendeds,
colleges,
applicant_degree_1,
applicant_degree_2,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id ORDER BY id LIMIT 1 ) AS applicant_degree_1_applicant_major_1,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id ORDER BY id LIMIT 1 OFFSET 1) AS applicant_degree_1_applicant_major_2,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_1).id ORDER BY minor_number LIMIT 1 ) AS applicant_degree_1_applicant_minor_1,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_1).id ORDER BY minor_number LIMIT 1 OFFSET 1) AS applicant_degree_1_applicant_minor_2,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_2).id ORDER BY id LIMIT 1 ) AS applicant_degree_2_applicant_major_1,
(SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_2).id ORDER BY id LIMIT 1 OFFSET 1) AS applicant_degree_2_applicant_major_2,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_2).id ORDER BY minor_number LIMIT 1 ) AS applicant_degree_2_applicant_minor_1,
(SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_2).id ORDER BY minor_number LIMIT 1 OFFSET 1) AS applicant_degree_2_applicant_minor_2
FROM (SELECT (college_attendeds.*)::college_attendeds AS college_attendeds,(colleges.*)::college_bases AS colleges,
(SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1 ) AS applicant_degree_1,
(SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1 OFFSET 1) AS applicant_degree_2
FROM college_attendeds
LEFT OUTER JOIN college_bases AS colleges ON (college_attendeds).college_id = colleges.id AND colleges.type = 'College') AS applicant_degree_data) AS college_attended_data
LEFT OUTER JOIN degree_bases AS applicant_degree_1_degree ON (applicant_degree_1).degree_id = applicant_degree_1_degree.id AND applicant_degree_1_degree.type IN ('Degree', 'PtDegree')
LEFT OUTER JOIN degree_bases AS applicant_degree_2_degree ON (applicant_degree_2).degree_id = applicant_degree_2_degree.id AND applicant_degree_2_degree.type IN ('Degree', 'PtDegree');
sql-server
sql-server
edited Jan 21 at 21:10
a_horse_with_no_name
40.1k776112
asked Aug 3 '16 at 19:05
sebeidsebeid
6721817
edited Jan 21 at 21:10
a_horse_with_no_name
40.1k776112
asked Aug 3 '16 at 19:05
sebeidsebeid
6721817
edited Jan 21 at 21:10
a_horse_with_no_name
40.1k776112
edited Jan 21 at 21:10
a_horse_with_no_name
40.1k776112
edited Jan 21 at 21:10
a_horse_with_no_name
40.1k776112
40.1k776112
asked Aug 3 '16 at 19:05
sebeidsebeid
6721817
asked Aug 3 '16 at 19:05
sebeidsebeid
6721817
asked Aug 3 '16 at 19:05
sebeidsebeid
6721817
6721817
bumped to the homepage by Community♦ 26 secs ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 26 secs ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
What isapplicant_degree_1?
– ypercubeᵀᴹ
Aug 3 '16 at 19:09
2
The cast(applicant_majors.*)::applicant_majorsis pretty much useless and a complicated way of writing*. The(applicant_degree_1).idlooks pretty strange. That would only be valid ifapplicant_degree_1was a composite type - which is not available in SQL Server.
– a_horse_with_no_name
Aug 3 '16 at 19:10
update the question with the whole query to include applicant_degree_1
– sebeid
Aug 3 '16 at 19:19
That doesn't look like a whole query. No query starts with aFROM.
– ypercubeᵀᴹ
Aug 3 '16 at 19:47
i didn't add the selected fields .
– sebeid
Aug 3 '16 at 19:48
add a comment |
What isapplicant_degree_1?
– ypercubeᵀᴹ
Aug 3 '16 at 19:09
2
The cast(applicant_majors.*)::applicant_majorsis pretty much useless and a complicated way of writing*. The(applicant_degree_1).idlooks pretty strange. That would only be valid ifapplicant_degree_1was a composite type - which is not available in SQL Server.
– a_horse_with_no_name
Aug 3 '16 at 19:10
update the question with the whole query to include applicant_degree_1
– sebeid
Aug 3 '16 at 19:19
That doesn't look like a whole query. No query starts with aFROM.
– ypercubeᵀᴹ
Aug 3 '16 at 19:47
i didn't add the selected fields .
– sebeid
Aug 3 '16 at 19:48
What is
applicant_degree_1?– ypercubeᵀᴹ
Aug 3 '16 at 19:09
What is
applicant_degree_1?– ypercubeᵀᴹ
Aug 3 '16 at 19:09
2
2
The cast
(applicant_majors.*)::applicant_majors is pretty much useless and a complicated way of writing *. The (applicant_degree_1).id looks pretty strange. That would only be valid if applicant_degree_1 was a composite type - which is not available in SQL Server.– a_horse_with_no_name
Aug 3 '16 at 19:10
The cast
(applicant_majors.*)::applicant_majors is pretty much useless and a complicated way of writing *. The (applicant_degree_1).id looks pretty strange. That would only be valid if applicant_degree_1 was a composite type - which is not available in SQL Server.– a_horse_with_no_name
Aug 3 '16 at 19:10
update the question with the whole query to include applicant_degree_1
– sebeid
Aug 3 '16 at 19:19
update the question with the whole query to include applicant_degree_1
– sebeid
Aug 3 '16 at 19:19
That doesn't look like a whole query. No query starts with a
FROM.– ypercubeᵀᴹ
Aug 3 '16 at 19:47
That doesn't look like a whole query. No query starts with a
FROM.– ypercubeᵀᴹ
Aug 3 '16 at 19:47
i didn't add the selected fields .
– sebeid
Aug 3 '16 at 19:48
i didn't add the selected fields .
– sebeid
Aug 3 '16 at 19:48
add a comment |
1 Answer
1
active
oldest
votes
0
applicant_degree_1 its a subquery that contains:
SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees
WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1
The query above try to get the greatest id from
applicant_degreesfor eachcollege_attended_id.
Then, I beleive your query can run on MSSQL like this:
SELECT applicant_majors.*
FROM applicant_majors
WHERE applicant_majors.applicant_degree_id IN (
SELECT MAX(id) FROM applicant_degrees
WHERE applicant_degrees.college_attended_id = college_attendeds.id
)
ORDER BY id
LIMIT 1 OFFSET 1
Notice that I changed the
ORDER BYandLIMITfor aMAXaggregate function.
Hope it helps.
answered Aug 3 '16 at 19:59
Sebastian WebberSebastian Webber
607210
Is LIMIT 1 OFFSET 1 works in SQL server
– sebeid
Aug 3 '16 at 20:29
what about this line SELECT (college_attendeds.*)::college_attendeds AS college_attendeds, (colleges.*)::college_bases AS colleges, how can we As with select * ?
– sebeid
Aug 3 '16 at 20:34
yes @sebeid, it works. I change theLIMIT|OFFSETbyMAX()because makes more sense using the function to get the max value.
– Sebastian Webber
Aug 3 '16 at 21:24
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f145796%2fquery-from-postgres-to-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
applicant_degree_1 its a subquery that contains:
SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees
WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1
The query above try to get the greatest id from
applicant_degreesfor eachcollege_attended_id.
Then, I beleive your query can run on MSSQL like this:
SELECT applicant_majors.*
FROM applicant_majors
WHERE applicant_majors.applicant_degree_id IN (
SELECT MAX(id) FROM applicant_degrees
WHERE applicant_degrees.college_attended_id = college_attendeds.id
)
ORDER BY id
LIMIT 1 OFFSET 1
Notice that I changed the
ORDER BYandLIMITfor aMAXaggregate function.
Hope it helps.
answered Aug 3 '16 at 19:59
Sebastian WebberSebastian Webber
607210
Is LIMIT 1 OFFSET 1 works in SQL server
– sebeid
Aug 3 '16 at 20:29
what about this line SELECT (college_attendeds.*)::college_attendeds AS college_attendeds, (colleges.*)::college_bases AS colleges, how can we As with select * ?
– sebeid
Aug 3 '16 at 20:34
yes @sebeid, it works. I change theLIMIT|OFFSETbyMAX()because makes more sense using the function to get the max value.
– Sebastian Webber
Aug 3 '16 at 21:24
add a comment |
0
applicant_degree_1 its a subquery that contains:
SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees
WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1
The query above try to get the greatest id from
applicant_degreesfor eachcollege_attended_id.
Then, I beleive your query can run on MSSQL like this:
SELECT applicant_majors.*
FROM applicant_majors
WHERE applicant_majors.applicant_degree_id IN (
SELECT MAX(id) FROM applicant_degrees
WHERE applicant_degrees.college_attended_id = college_attendeds.id
)
ORDER BY id
LIMIT 1 OFFSET 1
Notice that I changed the
ORDER BYandLIMITfor aMAXaggregate function.
Hope it helps.
answered Aug 3 '16 at 19:59
Sebastian WebberSebastian Webber
607210
Is LIMIT 1 OFFSET 1 works in SQL server
– sebeid
Aug 3 '16 at 20:29
what about this line SELECT (college_attendeds.*)::college_attendeds AS college_attendeds, (colleges.*)::college_bases AS colleges, how can we As with select * ?
– sebeid
Aug 3 '16 at 20:34
yes @sebeid, it works. I change theLIMIT|OFFSETbyMAX()because makes more sense using the function to get the max value.
– Sebastian Webber
Aug 3 '16 at 21:24
add a comment |
0
0
0
applicant_degree_1 its a subquery that contains:
SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees
WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1
The query above try to get the greatest id from
applicant_degreesfor eachcollege_attended_id.
Then, I beleive your query can run on MSSQL like this:
SELECT applicant_majors.*
FROM applicant_majors
WHERE applicant_majors.applicant_degree_id IN (
SELECT MAX(id) FROM applicant_degrees
WHERE applicant_degrees.college_attended_id = college_attendeds.id
)
ORDER BY id
LIMIT 1 OFFSET 1
Notice that I changed the
ORDER BYandLIMITfor aMAXaggregate function.
Hope it helps.
answered Aug 3 '16 at 19:59
Sebastian WebberSebastian Webber
607210
applicant_degree_1 its a subquery that contains:
SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees
WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1
The query above try to get the greatest id from
applicant_degreesfor eachcollege_attended_id.
Then, I beleive your query can run on MSSQL like this:
SELECT applicant_majors.*
FROM applicant_majors
WHERE applicant_majors.applicant_degree_id IN (
SELECT MAX(id) FROM applicant_degrees
WHERE applicant_degrees.college_attended_id = college_attendeds.id
)
ORDER BY id
LIMIT 1 OFFSET 1
Notice that I changed the
ORDER BYandLIMITfor aMAXaggregate function.
Hope it helps.
answered Aug 3 '16 at 19:59
Sebastian WebberSebastian Webber
607210
edited Aug 4 '16 at 17:03
answered Aug 3 '16 at 19:59
Sebastian WebberSebastian Webber
607210
answered Aug 3 '16 at 19:59
Sebastian WebberSebastian Webber
607210
answered Aug 3 '16 at 19:59
Sebastian WebberSebastian Webber
607210
607210
Is LIMIT 1 OFFSET 1 works in SQL server
– sebeid
Aug 3 '16 at 20:29
what about this line SELECT (college_attendeds.*)::college_attendeds AS college_attendeds, (colleges.*)::college_bases AS colleges, how can we As with select * ?
– sebeid
Aug 3 '16 at 20:34
yes @sebeid, it works. I change theLIMIT|OFFSETbyMAX()because makes more sense using the function to get the max value.
– Sebastian Webber
Aug 3 '16 at 21:24
add a comment |
Is LIMIT 1 OFFSET 1 works in SQL server
– sebeid
Aug 3 '16 at 20:29
what about this line SELECT (college_attendeds.*)::college_attendeds AS college_attendeds, (colleges.*)::college_bases AS colleges, how can we As with select * ?
– sebeid
Aug 3 '16 at 20:34
yes @sebeid, it works. I change theLIMIT|OFFSETbyMAX()because makes more sense using the function to get the max value.
– Sebastian Webber
Aug 3 '16 at 21:24
Is LIMIT 1 OFFSET 1 works in SQL server
– sebeid
Aug 3 '16 at 20:29
Is LIMIT 1 OFFSET 1 works in SQL server
– sebeid
Aug 3 '16 at 20:29
what about this line SELECT (college_attendeds.*)::college_attendeds AS college_attendeds, (colleges.*)::college_bases AS colleges, how can we As with select * ?
– sebeid
Aug 3 '16 at 20:34
what about this line SELECT (college_attendeds.*)::college_attendeds AS college_attendeds, (colleges.*)::college_bases AS colleges, how can we As with select * ?
– sebeid
Aug 3 '16 at 20:34
yes @sebeid, it works. I change the
LIMIT|OFFSET by MAX() because makes more sense using the function to get the max value.– Sebastian Webber
Aug 3 '16 at 21:24
yes @sebeid, it works. I change the
LIMIT|OFFSET by MAX() because makes more sense using the function to get the max value.– Sebastian Webber
Aug 3 '16 at 21:24
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f145796%2fquery-from-postgres-to-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What is
applicant_degree_1?– ypercubeᵀᴹ
Aug 3 '16 at 19:09
2
The cast
(applicant_majors.*)::applicant_majorsis pretty much useless and a complicated way of writing*. The(applicant_degree_1).idlooks pretty strange. That would only be valid ifapplicant_degree_1was a composite type - which is not available in SQL Server.– a_horse_with_no_name
Aug 3 '16 at 19:10
update the question with the whole query to include applicant_degree_1
– sebeid
Aug 3 '16 at 19:19
That doesn't look like a whole query. No query starts with a
FROM.– ypercubeᵀᴹ
Aug 3 '16 at 19:47
i didn't add the selected fields .
– sebeid
Aug 3 '16 at 19:48