Query to return first row based on condition
I would like to get the first row from the table only when the condition gets satisfied. The table has multiple entries and only when the particular value of a column is present at the top of the table, the query should return the output else the query should not return any row.
SELECT (CASE WHEN COL1 = 'AAA' THEN COL1 ELSE NULL END) AS A
FROM TABLE
QUALIFY ROW_NUMBER () OVER (ORDER BY TIMESTAMP DESC) = 1
WHERE A IS NOT NULL
If the value of the column is 'AAA' present at the top of the table then it should return the output, if not then no row should be returned. I am still getting the output as 'AAA' even when the first row is not 'AAA'.
teradata
edited Dec 28 '18 at 15:10
James Z
11.1k71835
asked Dec 28 '18 at 7:40
madsthirumadsthiru
3018
add a comment |
I would like to get the first row from the table only when the condition gets satisfied. The table has multiple entries and only when the particular value of a column is present at the top of the table, the query should return the output else the query should not return any row.
SELECT (CASE WHEN COL1 = 'AAA' THEN COL1 ELSE NULL END) AS A
FROM TABLE
QUALIFY ROW_NUMBER () OVER (ORDER BY TIMESTAMP DESC) = 1
WHERE A IS NOT NULL
If the value of the column is 'AAA' present at the top of the table then it should return the output, if not then no row should be returned. I am still getting the output as 'AAA' even when the first row is not 'AAA'.
teradata
edited Dec 28 '18 at 15:10
James Z
11.1k71835
asked Dec 28 '18 at 7:40
madsthirumadsthiru
3018
add a comment |
I would like to get the first row from the table only when the condition gets satisfied. The table has multiple entries and only when the particular value of a column is present at the top of the table, the query should return the output else the query should not return any row.
SELECT (CASE WHEN COL1 = 'AAA' THEN COL1 ELSE NULL END) AS A
FROM TABLE
QUALIFY ROW_NUMBER () OVER (ORDER BY TIMESTAMP DESC) = 1
WHERE A IS NOT NULL
If the value of the column is 'AAA' present at the top of the table then it should return the output, if not then no row should be returned. I am still getting the output as 'AAA' even when the first row is not 'AAA'.
teradata
edited Dec 28 '18 at 15:10
James Z
11.1k71835
asked Dec 28 '18 at 7:40
madsthirumadsthiru
3018
I would like to get the first row from the table only when the condition gets satisfied. The table has multiple entries and only when the particular value of a column is present at the top of the table, the query should return the output else the query should not return any row.
SELECT (CASE WHEN COL1 = 'AAA' THEN COL1 ELSE NULL END) AS A
FROM TABLE
QUALIFY ROW_NUMBER () OVER (ORDER BY TIMESTAMP DESC) = 1
WHERE A IS NOT NULL
If the value of the column is 'AAA' present at the top of the table then it should return the output, if not then no row should be returned. I am still getting the output as 'AAA' even when the first row is not 'AAA'.
teradata
teradata
edited Dec 28 '18 at 15:10
James Z
11.1k71835
asked Dec 28 '18 at 7:40
madsthirumadsthiru
3018
edited Dec 28 '18 at 15:10
James Z
11.1k71835
asked Dec 28 '18 at 7:40
madsthirumadsthiru
3018
edited Dec 28 '18 at 15:10
James Z
11.1k71835
edited Dec 28 '18 at 15:10
James Z
11.1k71835
edited Dec 28 '18 at 15:10
James Z
11.1k71835
11.1k71835
asked Dec 28 '18 at 7:40
madsthirumadsthiru
3018
asked Dec 28 '18 at 7:40
madsthirumadsthiru
3018
asked Dec 28 '18 at 7:40
madsthirumadsthiru
3018
3018
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You must move your WHERE-condition to QUALIFY:
SELECT *
FROM TABLE
QUALIFY ROW_NUMBER () OVER (ORDER BY TIMESTAMP DESC) = 1
AND COL1 = 'AAA'
answered Dec 28 '18 at 10:01
dnoethdnoeth
44.8k31838
add a comment |
This query should get the first row from the table and see if the COL1 is equal to "AAA."
SELECT * FROM (SELECT * FROM TABLE ORDER BY TIMESTAMP DESC LIMIT 1) WHERE COL1 = 'AAA'
answered Dec 28 '18 at 8:01
NahiyanNahiyan
294211
1
I tried this query and it worked SELECT * FROM (SELECT TOP 1 * FROM TABLE ORDER BY TIMESTAMP DESC) A WHERE COL1 = 'AAA' Thanks for your help
– madsthiru
Dec 28 '18 at 8:50
Great! Glad to see that your solution worked.
– Nahiyan
Dec 28 '18 at 8:56
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});
}
});
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%2fstackoverflow.com%2fquestions%2f53955193%2fquery-to-return-first-row-based-on-condition%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You must move your WHERE-condition to QUALIFY:
SELECT *
FROM TABLE
QUALIFY ROW_NUMBER () OVER (ORDER BY TIMESTAMP DESC) = 1
AND COL1 = 'AAA'
answered Dec 28 '18 at 10:01
dnoethdnoeth
44.8k31838
add a comment |
You must move your WHERE-condition to QUALIFY:
SELECT *
FROM TABLE
QUALIFY ROW_NUMBER () OVER (ORDER BY TIMESTAMP DESC) = 1
AND COL1 = 'AAA'
answered Dec 28 '18 at 10:01
dnoethdnoeth
44.8k31838
add a comment |
You must move your WHERE-condition to QUALIFY:
SELECT *
FROM TABLE
QUALIFY ROW_NUMBER () OVER (ORDER BY TIMESTAMP DESC) = 1
AND COL1 = 'AAA'
answered Dec 28 '18 at 10:01
dnoethdnoeth
44.8k31838
You must move your WHERE-condition to QUALIFY:
SELECT *
FROM TABLE
QUALIFY ROW_NUMBER () OVER (ORDER BY TIMESTAMP DESC) = 1
AND COL1 = 'AAA'
answered Dec 28 '18 at 10:01
dnoethdnoeth
44.8k31838
answered Dec 28 '18 at 10:01
dnoethdnoeth
44.8k31838
answered Dec 28 '18 at 10:01
dnoethdnoeth
44.8k31838
answered Dec 28 '18 at 10:01
dnoethdnoeth
44.8k31838
44.8k31838
add a comment |
add a comment |
This query should get the first row from the table and see if the COL1 is equal to "AAA."
SELECT * FROM (SELECT * FROM TABLE ORDER BY TIMESTAMP DESC LIMIT 1) WHERE COL1 = 'AAA'
answered Dec 28 '18 at 8:01
NahiyanNahiyan
294211
1
I tried this query and it worked SELECT * FROM (SELECT TOP 1 * FROM TABLE ORDER BY TIMESTAMP DESC) A WHERE COL1 = 'AAA' Thanks for your help
– madsthiru
Dec 28 '18 at 8:50
Great! Glad to see that your solution worked.
– Nahiyan
Dec 28 '18 at 8:56
add a comment |
This query should get the first row from the table and see if the COL1 is equal to "AAA."
SELECT * FROM (SELECT * FROM TABLE ORDER BY TIMESTAMP DESC LIMIT 1) WHERE COL1 = 'AAA'
answered Dec 28 '18 at 8:01
NahiyanNahiyan
294211
1
I tried this query and it worked SELECT * FROM (SELECT TOP 1 * FROM TABLE ORDER BY TIMESTAMP DESC) A WHERE COL1 = 'AAA' Thanks for your help
– madsthiru
Dec 28 '18 at 8:50
Great! Glad to see that your solution worked.
– Nahiyan
Dec 28 '18 at 8:56
add a comment |
This query should get the first row from the table and see if the COL1 is equal to "AAA."
SELECT * FROM (SELECT * FROM TABLE ORDER BY TIMESTAMP DESC LIMIT 1) WHERE COL1 = 'AAA'
answered Dec 28 '18 at 8:01
NahiyanNahiyan
294211
This query should get the first row from the table and see if the COL1 is equal to "AAA."
SELECT * FROM (SELECT * FROM TABLE ORDER BY TIMESTAMP DESC LIMIT 1) WHERE COL1 = 'AAA'
answered Dec 28 '18 at 8:01
NahiyanNahiyan
294211
edited Dec 28 '18 at 12:02
answered Dec 28 '18 at 8:01
NahiyanNahiyan
294211
answered Dec 28 '18 at 8:01
NahiyanNahiyan
294211
answered Dec 28 '18 at 8:01
NahiyanNahiyan
294211
294211
1
I tried this query and it worked SELECT * FROM (SELECT TOP 1 * FROM TABLE ORDER BY TIMESTAMP DESC) A WHERE COL1 = 'AAA' Thanks for your help
– madsthiru
Dec 28 '18 at 8:50
Great! Glad to see that your solution worked.
– Nahiyan
Dec 28 '18 at 8:56
add a comment |
1
I tried this query and it worked SELECT * FROM (SELECT TOP 1 * FROM TABLE ORDER BY TIMESTAMP DESC) A WHERE COL1 = 'AAA' Thanks for your help
– madsthiru
Dec 28 '18 at 8:50
Great! Glad to see that your solution worked.
– Nahiyan
Dec 28 '18 at 8:56
1
1
I tried this query and it worked SELECT * FROM (SELECT TOP 1 * FROM TABLE ORDER BY TIMESTAMP DESC) A WHERE COL1 = 'AAA' Thanks for your help
– madsthiru
Dec 28 '18 at 8:50
I tried this query and it worked SELECT * FROM (SELECT TOP 1 * FROM TABLE ORDER BY TIMESTAMP DESC) A WHERE COL1 = 'AAA' Thanks for your help
– madsthiru
Dec 28 '18 at 8:50
Great! Glad to see that your solution worked.
– Nahiyan
Dec 28 '18 at 8:56
Great! Glad to see that your solution worked.
– Nahiyan
Dec 28 '18 at 8:56
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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%2fstackoverflow.com%2fquestions%2f53955193%2fquery-to-return-first-row-based-on-condition%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
